Jump Start: Stored Procedure Templates

In Jump Start, "Stored Procedures", I explained the basics of creating and executing stored procedures. Another way to easily create stored procedures--especially for those who aren't very familiar with them and with T-SQL--is by using the Template Explorer feature in SQL Server Management Studio Express (SSMSE).

To use Template Explorer, open SSMSE and select the View, Template Explorer option. In the Template Explorer window, expand the Stored Procedures node, then double-click Create Procedure Basic Template. SSMSE will generate the following CREATE PROCEDURE T-SQL code:

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

-- Create a basic stored procedure template.--

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

-- Drop stored procedure if it already exists.IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'‹Schema_Name, sysname, Schema_Name›' AND SPECIFIC_NAME = N'‹Procedure_Name, sysname, Procedure_Name›') DROP PROCEDURE ‹Schema_Name, sysname, Schema_Name›.‹Procedure_Name, sysname, Procedure_Name›GOCREATE PROCEDURE ‹Schema_Name, sysname, Schema_Name›.‹Procedure_Name, sysname, Procedure_Name›‹@param1, sysname, @p1› ‹datatype_for_param1, , int› = ‹default_value_for_param1, , 0›, ‹@param2, sysname, @p2› ‹datatype_for_param2, , int› = ‹default_value_for_param2, , 0›ASSELECT @p1, @p2GO--

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

-- Execute the stored procedure.--

========================================EXECUTE ‹Schema_Name, sysname, Schema_Name›.‹Procedure_Name, sysname, Procedure_Name› ‹value_for_param1, , 1›, ‹value_for_param2, , 2›GO

To use the generated stored procedure template, the first thing you need to do is replace the placeholder codes that are in angle brackets with the names you want to use. The first placeholder code,

‹Schema_Name, sysname, Schema_Name›

is replaced in its entirety with the name of the schema you want to use. If you don't specify a schema, the dbo schema will be used by default. The second placeholder code,

‹Procedure_Name, sysname, Procedure_Name›

is replaced in its entirety with the name of your stored procedure--for example, usp_MyStoredProc.

You use the lines beneath the CREATE PROCEDURE statement for optional parameters, as I'll explain in a later column. For a simple stored procedure that doesn't use parameters, you can safely delete those lines.

Finally, you replace the line

SELECT @p1, @p2

with the custom code that your stored procedure will use. You can then use Query Editor to execute the stored procedure.

I'll discuss more details of this template in my next Jump Start column.