Developing T-SQL Stored Procedures to Deal with Lookup Tables - How to update/delete a row in a table using a T-SQL stored procedure
(Page 2 of 5 )
The following is the stored procedure which deals with updating a particular row in a table.
CREATEPROCEDURE [dbo].[p_emp_Update]
@empno int,
@ename varchar(50),
@sal float,
@deptno int
AS
UPDATE[dbo].[emp] SET
[ename] = @ename,
[sal] = @sal,
[deptno] = @deptno
WHERE
[empno] = @empno
RETURN
The above is a stored procedure named “p_emp_update” which accepts four parameters (according to the columns available in the table). Once all the parameters are passed in, it executes an UPDATE statement to replace the existing values in a row identified by the given employee number.
To test the above stored procedure, you can issue the following statement in Query Analyzer or SQL Server Management Studio.
executedbo.p_emp_Update 2001, 'bbb', 5600, 10
Once you execute the above statement, it will automatically modify the information of employee identified with the employee number 2001. Deleting a row is very similar to the above; it could be written something like this:
CREATEPROCEDURE [dbo].[p_emp_Delete]
@empno int
AS
DELETEFROM [dbo].[emp]
WHERE
[empno] = @empno
RETURN
You can observe that to delete a row (or employee), the employee number alone is enough. That is the reason I have only one parameter to pass to the above stored procedure. To execute the above store procedure, you can give a command something like the following:
executedbo.p_emp_Delete 2001
How to insert/update using a single T-SQL stored procedure
Let us consider that you wanted to insert a row, if it's not available, or update a row, if it is available, using a single stored procedure. The following is the stored procedure which deals with this:
CREATEPROCEDURE [dbo].[p_emp_save]
@empno int,
@ename varchar(50),
@sal float,
@deptno int
AS
IFEXISTS(SELECT [empno] FROM [dbo].[emp] WHERE [empno] = @empno)
BEGIN
UPDATE [dbo].[emp] SET
[ename] = @ename,
[sal] = @sal,
[deptno] = @deptno
WHERE
[empno] = @empno
END
ELSE
BEGIN
INSERT INTO [dbo].[emp] (
[empno],
[ename],
[sal],
[deptno]
) VALUES (
@empno,
@ename,
@sal,
@deptno
END
RETURN
The above is a stored procedure named “p_emp_save” which accepts four parameters (according to the columns available in the table). Once all the parameters are passed in, it executes an INSERT statement if the given employee number does not exist within the table. It executes an UPDATE statement if a row exists with the given employee number.
To test the above stored procedure, you can issue the following statement in Query Analyzer or SQL Server Management Studio.
executedbo.p_emp_save 2002, 'aaa', 4500, 20
Once you execute the above, it will insert a row with employee number 2002 (if it doesn’t exist).
executedbo.p_emp_save 2002, 'bbb', 5400, 10
Once you execute the above, just after the previous one, you will see that the values are updated instead of inserted.
Next: How to retrieve one or all rows in a table using a T-SQL stored procedure >>
More ASP.NET Articles
More By Jagadish Chaterjee
