Jump Start: Inside Stored Procedure Templates

In "Jump Start, "Stored Procedure Templates" (www.sqlmag.com/Article/ArticleID/97025 ), I demonstrated how you can take advantage of the Templates feature in SQL Server Management Studio Express (SSMSE) to quickly and easily create a T-SQL template that you can then use to create new stored procedures. In that column, I explained the essential template elements that you need to change in order to use the template for your own stored procedures. (For more information on what a stored procedure is, see Jump Start, "Stored Procedures," www.sqlmag.com/Article/ArticleID/96853 .)

Now I want to explain what the code does. We'll use the following sample completed template:

-- =========================================

-- MyNwdSP - This stored procedure retrieves from -- the Customers table the rows for which the -- value in the City column equals the value -- supplied in parameter 1. --

====================================

-- Drop the stored procedure if it already exists. IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'MyNwdSP' ) DROP PROCEDURE MyNwdSP GO CREATE PROCEDURE MyNwdSP @P1city nvarchar(15) AS SELECT * FROM Customers WHERE city = @P1city GO --

====================================

-- Execute the stored procedure. --

=========================================EXECUTE MyNwdSP 'London'GO

The first six lines that begin with double hyphens (--) are comments. You'd typically use this area to state the name of your stored procedure and provide a brief description of what it does.

The IF EXISTS statement checks for the existence of the stored procedure in the system catalog. This step is necessary because you can't create a stored procedure if one with the same name already exists. If a similarly named stored procedure is found, it's dropped (i.e., deleted).

The CREATE PROCEDURE statement creates the stored procedure. In the sample above, the stored procedure takes one parameter, P1city, which is an nvarchar(15) data type.

The body of the stored procedure follows the line that begins with AS. Complex stored procedures can contain hundreds of lines. This simple example uses just one SELECT statement, which retrieves all rows from the Customers table for which the value in the City column equals the value passed by the P1city parameter. Running this code in Query Editor creates the stored procedure.

The line that begins with EXECUTE shows how to run the stored procedure and pass it a parameter. In the sample code, the EXECUTE statement is followed by the name of the stored procedure (i.e., MyNwdSP) and the value you want to pass in the P1city parameter (i.e., London). Because this value is a literal, you need to put it between two apostrophes ('). Running this code in Query Editor executes the stored procedure and returns a result set of all the rows in which the City column contains the value London.