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.

Comments

Comments can be added to stored procedure to describe the script. These are non-functional and for reference purposes only. Comments in stored procedures can be defined in two ways. Firstly, placing two consecutive hyphens (--) on a line signifies that the remainder of the line is a comment. Secondly, a block of text that spans lines can be identified as a comment by placing it between two special markers. The start-of-comment marker is a forward slash and an asterisk (/*). The end of the comment is marked with the same characters but this time reversed (*/).

-- This is a comment!

/* This block of text is
   also a comment! */

The stored procedure template includes several comments, including two large blocks. The first block will not be stored when the procedure is created. It provides some information for the creator of the new stored procedure.

The second block of comments is included when the stored procedure is saved to the database. It is optional, so can be removed if you would prefer not to keep it. The comments here are used to specify information about the new script, including the author, creation date and a brief description. You can type this additional information manually or use the "Specify Values for Template Parameters" dialog box to make the task easier. To display the box, press Ctrl-Shift-M.

The dialog box shows a grid containing a row for each comment item, the stored procedure name and information for the two parameter placeholders that are included in the template. In this case, enter your name in the Value column for the Author row. Add the current date and enter "Retrieves details of contract jobs." for a description. We can also provide a procedure name, in this case "GetContractJobs". Click the OK button to return to the query window. You should see that the information you entered has been included in the template.

CREATE PROCEDURE Command

The CREATE PROCEDURE command is responsible for the creation of the stored procedure. For our first script, we will not require any parameters so remove the template parameters from the statement. You can also remove the comments that appear within the body of the procedure (between the BEGIN and END commands). The command should now appear as follows:

CREATE PROCEDURE GetContractJobs AS
BEGIN
    SET NOCOUNT ON;

    SELECT @p1, @p2
END

SET NOCOUNT ON

When you execute a T-SQL statement, the number of rows returned or affected by the command is added to the results. In SSMS, this information is displayed beneath the query's results. However, this additional information is often unused and becomes additional network traffic. Worse, for some systems it can actually cause problems. For this reason, the default option in a new stored procedure created using the template is to disable this text using the "SET NOCOUNT ON" command.

Creating a Simple Stored Procedure

With the template almost complete, we can now add the body of the stored procedure. This contains the commands that will be executed whenever the procedure is called. For our first example we will create a very simple script that queries the indexed view that we created in the previous article.

Replace the template's SELECT statement with the following code, ensuring that you retain the SET NOCOUNT ON and END elements:

SELECT
    ContractNumber,
    JobName,
    StandardPrice,
    ContractValue
FROM
    ContractJobs

To create the stored procedure and save it in the database for future use, execute the script by pressing F5. A success message will be displayed and if you refresh the Stored Procedures section of the Object Explorer you will see the new stored procedure. Once you have created the new object, close the query window.