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 Stored Procedure

Now that we have a stored procedure, we can execute it simply by typing its name into a new query window and pressing F5. Open a new query window and run the following. You should see the results of the stored query displayed.

GetContractJobs

This syntax works because the stored procedure is the only command being executed. If you wish to call a stored procedure in a script that contains multiple statements, you must use the EXECUTE command, which may be abbreviated to EXEC, as in the following example:

EXECUTE GetContractJobs
EXEC GetContractJobs

Finally, we can use SSMS to generate the commands to execute a stored procedure. To do so, right-click the name of the stored procedure in the Object Explorer. From the context-sensitive menu, select "Execute Stored Procedure". A dialog box will be displayed. If the stored procedure had parameters, this dialog box would show them and permit you to specify the values that you wished to use for each. However, for this simple procedure there are no configuration options so click OK to generate the script.

DECLARE @return_value int

EXEC @return_value = [dbo].[GetContractJobs]

SELECT 'Return Value' = @return_value

The key statements in the new query are shown above. The DECLARE command creates a new variable named @return_value that can contain an integer value. All variables are named in this fashion with a prefix of "@". The variable will be assigned the return value from the stored procedure. In this case the return value will default to zero, as we have not specified any other value.

The second line executes the stored procedure using the EXEC keyword. You can see that the two-part naming convention is used when SSMS generates the commands for us. This line also assigns the return value of the stored procedure to the @return_value variable.

The final statement executes a new query. This query does not retrieve data from a table. Instead, it generates its own results by naming a column and selecting the value from the @return_value variable. In an SSMS query, this will add a second recordset to the results window. The second recordset will include a single row containing the return value. Press F5 to execute the query.

Returning a Value from a Stored Procedure

Stored procedures always return an integer value. As we have already seen, if the return value is not specified it will be zero. If a stored procedure can complete in a variety of possible states, it is useful to utilise the return value to specify the state according to a set of conditions. For example, the stored procedure may return zero for success or another value to indicate a warning or error. NB: It is conventional to return zero to indicate success. Other values may be a sign of success or failure or signify any other information that you wish.

Values are returned from a stored procedure using the RETURN command. This command halts execution of the stored procedure immediately, even if there are further commands in the batch. The RETURN statement used alone ends processing and returns zero. If you wish to return an alternative integer value, you supply it after the RETURN command.

To avoid overcomplicating this introductory article, we will not look at conditional processing until later in the tutorial. Instead, we will create a very simple stored procedure that does nothing except return the number fifty-two. Create the stored procedure by executing the following:

CREATE PROCEDURE ReturnValueDemo AS
BEGIN
    SET NOCOUNT ON
    RETURN 52
END

Use the "Execute Stored Procedure" option from the Object Explorer to generate the command to run the script and then press F5. You will see the return value in the results window.

Adding Parameters

Most stored procedures require information from the outside world to be passed to them to modify their behaviour. This information can be provided using parameters. Each parameter must be defined in the declaration area of the stored procedure and given a name and data type. The names follow the standard naming conventions for variables, as the parameters will be used as variables within the procedure.

In the next example, we will create a stored procedure that generates a new business customer in the JoBS database. This requires three pieces of information to be passed into the procedure so that the business name and the primary contact's first and last names can be specified. To add these parameters, they are provided as a comma-separated list between the name of the new script and the AS keyword. In each case, the name of the parameter is specified first, a space is included and the data type follows. The data types in this example will match those of the columns to be inserted into.

Once the parameters are defined, they can be used in place of literal values. In this case, we will replace the values in an insert statement with the parameter variables. To create the procedure that inserts new business customers, execute the following script:

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