Stored Procedures For Beginners

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