Stored Procedures For Beginners

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.