SQL 2005+

SQL Server Stored Procedures

by Richard Carr, published at http://www.blackwasp.co.uk/SQLStoredProcedures.aspx

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.

Executing a Parameterised Stored Procedure

There are several variations of syntax available when you wish to execute a stored procedure that includes parameters. The simplest requires that you provide the name of the procedure and a comma-separated list containing values for each parameter. The values must be in the same order as they are defined in the stored procedure. As with previous examples, if you wish to use the procedure as anything other than the first statement in a script, you must also precede the call with the EXEC command. Using the CreateBusinessCustomer, we can insert a new Customer row as follows:

CreateBusinessCustomer 'ABC Blinds', 'Fred', 'Green'

One problem with this syntax is that the stored procedure definition may be changed, causing the parameters to be incorrectly assigned. In many cases this will cause an error when you try to run the procedure. However, a worse situation could be that the invalid parameters are accepted and allow the stored procedure to apparently run correctly whilst generating invalid results. To avoid this problem, you can specify values for names parameters explicitly. You can then provide the parameters in an order that is different to that of the procedure's signature. A modified version of the above sample could be:

CreateBusinessCustomer
    @ContactLastName='Green',
    @ContactFirstName='Fred',
    @BusinessName='ABC Blinds'

This syntax permits the stored procedure parameters to be reordered without causing a problem. You can even add new parameters to the stored procedure without breaking the call, if the new parameters have default values.

Adding Default Parameter Values

Sometimes it is useful to include optional parameters in a stored procedure. These parameters do not need to be supplied when calling the script. If they are not provided, the parameters will automatically be populated with default values. A default value is specified by adding an equals sign (=) to the parameter name and supplying either a compatible value or NULL for the default. In the following sample, the procedure can be used to create business or non-business customers. For non-business customers, the @BusinessName parameter can be excluded from the call and will be automatically set to NULL.

CREATE PROCEDURE CreateCustomer 
    @BusinessName VARCHAR(100) = NULL,
    @ContactFirstName VARCHAR(25),
    @ContactLastName VARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO Customers
        (BusinessName, FirstName, LastName)
    VALUES
        (@BusinessName, @ContactFirstName, @ContactLastName)
END

To demonstrate executing the procedure without the @BusinessName parameter, try the following:

CreateCustomer
    @ContactFirstName='Jim',
    @ContactLastName='Jasper'

Adding Output Parameters

The parameters described above have all been input parameters. This means that the values supplied to the parameters can only be used to send information into the stored procedure and not out of it. If the values of the parameters are modified within the batch of statements, the changes are not seen outside of the procedure.

Sometimes it is useful to return multiple values to the calling routine. This can be achieved with output parameters. Output parameters are dual-purpose. They still work as a means to pass information into the stored procedure if desired. In addition, any changes to the values within the stored procedure are visible outside of the batch too.

We can demonstrate this with a very simple stored procedure. In this case we will not query the database. We will instead perform some basic mathematical operations. The following stored procedure includes two input parameters that are used to pass two integer values into the batch. It also includes two output parameters that can be used to return the sum and the product of those two values. As you can see, the output parameters are identified as such by adding the OUTPUT keyword after the parameter. To set a return value, the parameters are simply assigned.

NB: This is an unusual example of the use of a stored procedure but is useful for example purposes.

CREATE PROCEDURE OutputDemo 
    @Value1 INT,
    @Value2 INT,
    @Sum INT OUTPUT,
    @Product INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON
    SET @Sum = @Value1 + @Value2
    SET @Product = @Value1 * @Value2
END
GO

When you execute a stored procedure that includes output parameters you have the option of returning or ignoring their output. If you use that standard syntax to call the script the output parameters will be used as if they were input parameters. To allow the procedure to return output parameter values you must use variables for the parameters and you must also add the OUTPUT keyword to each parameter that will return an updated value.

We can see this by running the following script. The first statement runs the stored procedure and returns the desired values. The second statement shows the values in the results window. Try removing the OUTPUT keywords and running the script again to see that, without the OUTPUT, the parameters do not return updated values.

DECLARE @ReturnedSum INT
DECLARE @ReturnedProduct INT

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

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