Jump Start: Stored Procedures

Since March 2007 I've been covering the ins and outs of the SQL SELECT statement. Jump Start has explored variations of the SELECT statement ranging from basic single table SELECTs to multiple table joins. Now I'm going to change gears and have a look at another type of SQL Server database object: stored procedures. Just like its full-featured cousins SQL Server 2005 Standard Edition and Enterprise Edition, SQL Server 2005 Express provides complete support for stored procedures.

Stored procedures, which are essentially a batch of T-SQL statements that have been grouped together, are one of the core components of most production database applications. Using stored procedures makes a lot of sense when you have a query or other code that's executed repeatedly. Unlike a simple T-SQL batch, which can be stored and executed from a text file, a stored procedure needs to be created in the database before you use it. Logically, you create a new stored procedure using the CREATE PROCEDURE statement. The following code creates a simple stored procedure named sp_ListCustomers:

CREATE PROCEDURE sp_ListCustomersAS     SELECT * from CustomersGO

The stored procedure name follows the CREATE PROCEDURE statement. Here I used the name sp_ListCustomers. Using a prefix of sp_ (for "stored procedure") isn't required, but it's generally a good idea to follow some type of standard naming practice for your database objects. The stored procedure's executable code starts after AS. In this example, it's a one line SELECT statement that returns all rows and columns from the Customers table.

To execute the stored procedure you need to use the EXEC statement:

EXEC sp_ListCustomers

Creating and executing stored procedures are the first things you need to learn how to do if you're just learning about stored procedures. Obviously, they can get a lot more involved than this. In my first September column I'll dig a little deeper into SQL Server Express stored procedures.