BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Stored Procedures

The twenty-third part of the SQL Server Programming Fundamentals tutorial describes stored procedures. A stored procedure allows you to create a script, or batch, containing multiple commands to be executed, potentially being controlled by parameters.

Modifying a Stored Procedure

Stored procedures can be modified easily using the ALTER PROCEDURE command. This uses an almost identical syntax to the CREATE PROCEDURE statement. It permits you to modify the parameters and the body of the stored procedure. For example, we can add a new output parameter and a new calculation to the OutputDemo stored procedure as follows.

NB: The Abs command returns the absolute, positive value of a number; positive numbers are unchanged but negative values are made positive.

ALTER PROCEDURE OutputDemo
    @Value1 INT,
    @Value2 INT,
    @Sum INT OUTPUT,
    @Multiple INT OUTPUT,
    @Difference INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON
    SET @Sum = @Value1 + @Value2
    SET @Multiple = @Value1 * @Value2
    SET @Difference = abs(@Value1 - @Value2)
END

Once you have updated the stored procedure, you must also change the way that you call it to include the new parameter:

DECLARE @ReturnedSum INT
DECLARE @ReturnedProduct INT
DECLARE @ReturnedDifference INT

EXEC OutputDemo
    5,
    6,
    @ReturnedSum OUTPUT,
    @ReturnedProduct OUTPUT,
    @ReturnedDifference OUTPUT

SELECT
    'Sum' = @ReturnedSum,
    'Multiple' = @ReturnedProduct,
    'Difference' = @ReturnedDifference

Deleting a Stored Procedure

Stored procedures can be removed from a database using the DROP PROCEDURE command. The statement requires the name of the procedure to be deleted. To remove the OutputDemo script, execute the following:

DROP PROCEDURE OutputDemo
15 March 2009