SQL 2005+

SQL Server Stored Procedures (2)

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.

The Stored Procedure Template

Stored procedures are created using the T-SQL "CREATE PROCEDURE" command. This command is used to specify the name of the new batch, any parameters that are required and the code of the procedure itself. Although the command can be typed manually into a query window, SQL Server Management Studio (SSMS) provides a template to get you started. To display the template, open SSMS and expand the JoBS database in the Object Explorer. You should see a node in the tree named "Programmability". Expand this branch of the tree to see the available elements for programming SQL Server. In this case, right-click the Stored Procedures section and select "New Stored Procedure..." from the menu that appears. A new query window containing the skeleton of a stored procedure is opened. The template will be similar to that shown below (though may vary depending upon the specific version of SQL Server that you are using):

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
  -- Add the parameters for the stored procedure here
  <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_For_Param1, , 0>, 
  <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_For_Param2, , 0>
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

The template includes various items that are useful when creating a stored procedure. Some of the key elements are described in the following sections.

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.