Create a stored procedure that would Retrieve, Update, and Delete Records on each Table

Question

CREATE TABLE Person
(
ID INT Primary Key Identity (1,1),
LastName nVarchar (20) not NULL, 
FirstName nVarchar (20) not NULL, 
MiddleName nVarchar (20), 
BirthDate DateTime not NULL, 
Age INT not NULL, 
Check (Age>18) 
);


CREATE TABLE Department
(
ID INT Primary Key Identity (1,1), 
DepartmentName nVarchar (50) Unique, 
DepartmentCode nVarchar (20) Unique, 
IsActive Bit Default (1)
); 


CREATE TABLE Employee
(
ID INT Primary Key Identity (1,1), 
PersonId INT Foreign Key REFERENCES Person, 
DepartmentId INT Foreign Key REFERENCES Department, 
Salary Decimal (18,2), 
Check (Salary>10000), 
IsActive Bit Default (1) 
);

I tried using this but I`m not quite sure if this is correct.

    CREATE PROCEDURE sp_retrieve 
    AS 
    BEGIN 
    SELECT * FROM Person 
    END 
    GO

How do I create a stored procedure for retrieve, update and delete correctly?

That stored procedure only queries.... where is the delete or update statement part of it? How does the delete and update apply? — Brian Mains, Jul 16 '15 at 11:58
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Jul 16 '15 at 12:02

Dane · Accepted Answer · 2015-07-16T20:19:06.343

0

CREATE PROCEDURE usp_Test123
AS
SELECT 'This is a test'

CREATE PROCEDURE usp_Update @Col1Val NVARCHAR(MAX)
AS
UPDATE ReplaceTableNameHere
SET Column1 = @Col1Val

CREATE PROCEDURE usp_Insert @Col1Val NVARCHAR(MAX)
AS
INSERT INTO ReplaceTableNameHere (Col1)
VALUES (Column1 = @Col1Val)

CREATE PROCEDURE usp_delete @PKID NVARCHAR(MAX)
AS
DELETE ReplaceTableNameHere
WHERE ColumnPKID = @PKID

Edit: You would pass in the value when executing the stored procedure to allow for the select, update and delete.

Example for update:

EXEC usp_Update @Col1Val = 50;

This would update the table that you define in the stored procedure to the value of 50.

edited Jul 16 '15 at 20:19

answered Jul 16 '15 at 12:06

Dane

312
2
10

I assume you're asking for a template so the I made one for the statements as you said. As marc_s mentioned generally stay away from prefixing with sp, most people use usp (User). – Dane Jul 16 '15 at 12:07
Thanks for this but I don't get it. I changed it to these. Correct or no? CREATE PROCEDURE usp_update Col1Val NVARCHAR(MAX) AS UPDATE ReplacePerson SET PersonId = Col1Val CREATE PROCEDURE usp_Insert @Col1Val NVARCHAR(MAX) AS INSERT INTO ReplaceDepartment (Col1) VALUES (Column1 = @Col1Val) CREATE PROCEDURE usp_delete PKID NVARCHAR(MAX) AS DELETE ReplaceEmployee WHERE ColumnPKID = PKID – Jezun Jul 16 '15 at 18:01
Edited answer. Hope it's more clear. – Dane Jul 16 '15 at 20:19
So, that's it? The one you listed above? I should not make any changes? For example, DELETE ReplaceTableNameHere. Should I changed it or let it be? – Jezun Jul 17 '15 at 03:43
You need to replace the placeholder with your actual table name. I don't have time to do this sorry but if it's still not resolved tonight I can give you the code. – Dane Jul 17 '15 at 05:10
I think I got it now. Thanks. But if you can, please do. So I can make sure ;) – Jezun Jul 17 '15 at 06:45

Create a stored procedure that would Retrieve, Update, and Delete Records on each Table

1 Answers1