SQL Server Stored Procedures for Beginners

Writing Your First Stored Procedure

Finally!!! It is time to write your first stored procedure (assuming you have created your database). In SQL Server, under your database tree, select the "Stored Procedures" option from Enterprise Manager (when you gain more experience, you can use Query Analyzer to create stored procedures). There will be a number of system generated stored procedures there already. Just ignore them. Your next step is to right click on any of the existing stored procedures (don’t worry, you won’t actually use them), then select "New Stored Procedure . . ."  This will open the stored properties window I discussed above. The following code will appear already in the window:

CREATE PROCEDURE [PROCEDURE NAME] AS

The first thing I usually do is provide some spacing (we’ll need it later). This isn’t required, and as you write more stored procedures, you will find a style with which you are comfortable.

/*
We will use this area for comments
*/

CREATE PROCEDURE [PROCEDURE NAME]

 /*
We will put the variables in here, later
*/

AS

/*
This is where the actual SQL statements will go
*/

So far, it is pretty simple. Let’s look at the top comments section first,

/*
We will use this area for comments
*/

When you write stored procedures (especially for a business or academic project), you never know who will eventually have to alter the code. This top section is useful for comments about the stored procedure, a change log, and other pertinent information. While this is not required, it is just a good programming habit. For this exercise, make it look like this:

/*
Name:  usp_displayallusers
Description:  displays all records and columns in USERLIST table
Author:  Tom O’Neill
Modification Log: Change

Description                  Date         Changed By
Created procedure            7/15/2003    Tom O’Neill
*/

Of course, you can use your own name and today’s date.

The next section will change only slightly. Every stored procedure needs the words "CREATE PROCEDURE" followed by the name you want to assign to the stored procedure. While not required, stored procedure names usually begin with the prefix "usp_".

CREATE PROCEDURE usp_displayallusers

This tells the database that you are creating a stored procedure named "usp_displayallusers". So far, your stored procedure should look like this:

/*
Name:  usp_displayallusers
Description:  displays all records and columns in USERLIST table
Author:  Tom O’Neill
Modification Log: Change

Description                  Date         Changed By
Created procedure            7/15/2003    Tom O’Neill
*/

CREATE PROCEDURE usp_displayallusers

The next step is to think about variables. Since this is our first stored procedure together, we won’t deal with them yet. Just keep in mind that they are usually added after the "CREATE PROCEDURE" line. Since we don’t have variables, the next step is quite simple. Put the word "AS" beneath the create procedure line.

CREATE PROCEDURE usp_displayallusers
AS