By : Tom O'Neill
Learning something new is challenging. Learning something new on your own redefines challenging.
Chances are, you are reading my article because you want to learn how to write stored procedures. You are new to this, and you don't know where to start. You are exactly where I was when I wanted to learn how to write stored procedures. The purpose of this article is to help the developer who doesn't know where to start. I will give you a place to start, teach you techniques beyond the basic "hello world", and walk you through examples. I learned how to write stored procedures alone, and have decided to let you use my experiences to your advantage. This article will consist of the following topics:
1. What are stored procedures?
2. What do you need to get started?
3. Writing your first stored procedure
4. Writing more sophisticated stored procedures
a. Input Variables
b. Input Variables with SELECT and UPDATE Statements
c. Exercise: Pass Data to a SELECT Stored Procedure
5. Conclusion
6. Database script for SPFORBEGINNERS (the database referenced in this article)
At the end, I have also included a SQL script for creating the database used in my examples. It is a small database, and easily replicated. Of course, I am interested in receiving any feedback at tomoneill@deloitte.com.
What Are Stored Procedures?
Have you ever written SQL statements, like inserts, selects, and updates? Then you have already written most of a stored procedure. A stored procedure is an already written SQL statement that is saved in the database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure. When you put this SQL statement in a stored procedure, you can then run the stored procedure from the database's command environment (I am a SQL Server kind of guy, and run stored procedures from the Query Analyzer), using the exec command.
An example is:
exec usp_displayallusers
The name of the stored procedure is "usp_displayallusers", and "exec" tells SQL Server to execute the code in the stored procedure. (Note: "usp_" in front of the stored procedure name is used to designate this stored procedure as a user-created stored procedure.) The code inside the stored procedure can be something as simple as:
SELECT * FROM USERLIST
This "select" statement will return all data in the USERLIST table. You may think, skeptically, that stored procedures aren’t terribly useful. Just save the query and run it when you need to. Too easy, right?
Well, there is more to the story. Many queries get more complex than "select * from . . ." Also, you may want to call the stored procedure from an application, such as an ASP page, Visual Basic application, or a Java servlet. With a stored procedure, you can store all the logic in the database, and use a simple command to call the stored procedure. Later, if you decide to migrate from ASP to J2EE, you only need to change the application layer, which will be significantly easier. Much of the business logic will remain in the database.
Enough background—let’s write some stored procedures.
Getting Started with Stored Procedures
What do I need to get started? I have heard that question often. To begin writing stored procedures, the following are essential:
1. A database management system.
2. A database built inside the database management system (see the end of this article for a sample).
3. A text editor, such as Notepad or Query Analyzer.
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
*/
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
We are telling the database that we want to create a stored procedure that is called "usp_displayallusers" that is characterized by the code that follows. After the "AS" entry, you will simply enter SQL code as you would in a regularly query. For our first, we will use a SELECT statement:
SELECT * FROM USERLIST
Now, 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
AS
SELECT * FROM USERLIST
Congratulations, you have written your first stored procedure. If you authored the procedure in a text editor, now would be a good time to copy it into the New Stored Procedure window in SQL Server. Once you have done so, click the "Check Syntax" box. This is a great troubleshooting tool for beginners and experts alike. When SQL Server tells you "Syntax check successful!", you can click OK to save your stored procedure. To view the procedure, simply double-click usp_displayallusers in the Stored Procedures window. To run your stored procedure, open the Query Analyzer and type:
exec usp_displayallusers
Then, click the green "play" button to run the query. You will see that the procedure has run successfully.
It can be frustrating to start from scratch. Right now, you can think of all the things you want to accomplish with stored procedures; you just need to learn how! That will happen next. Let’s take a look at some more useful stored procedures.
Read More...
