Stored Procedures For Beginners

Items 1 and 2 are absolutely essential. You can’t write stored procedures without a database. They would be useless. Sometimes, I write my procedures in Notepad (or another text editor), and copy them into the New Stored Procedure window in SQL Server. The New Stored Procedure window is a bit small, and in Notepad I can spread things out a bit (you’ll see later).

Next, you will have to decide what you want your stored procedure to do. It can be tempting to just dive right into the task at hand, but it is always prudent to sketch out some ideas first. Some considerations should be:

• Do you want to view data in the database (SELECT), insert new records (INSERT INTO), or do I want to change an existing record (UPDATE)?

• With which tables will you have to work? Does it make sense to create a VIEW first?

• How often will this procedure actually be used?

Once you have struggled with these questions (something of an exaggeration, I guess), you will be ready to start coding!

Note: Throughout this article, I will focus on stored procedures for SQL Server. You can apply the same principles to other database management systems, but I will make clear references to working in a SQL Server environment

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
*/