Jump Start: Stored Procedures--The Big Picture

Starting with "Jump Start: Stored Procedures" (www.sqlmag.com/Article/ArticleID/96853 ) in August, I've been covering the basics of SQL Server Express stored procedures. In "Jump Start: Stored Procedure Templates" (www.sqlmag.com/Article/ArticleID/97025 ), I showed an easy way to create stored procedures using SQL Server Management Studio Express (SSMSE) templates, and in "Jump Start: Inside Stored Procedure Templates" (www.sqlmag.com/Article/ArticleID/97201 ), I explained the basic elements of the completed stored procedure. Now I want to take a step back and make sure that you get the big picture of SQL Server Express stored procedures.

First, it's important to realize that stored procedures are database objects, exactly like a table or view. As you saw in "Inside Stored Procedure Templates," you create stored procedures similarly to how you create a table or view: by executing a set of T-SQL statements.

Each database has its own set of stored procedures. There are two types of stored procedures: system stored procedures and user-created store procedures. When you create a new database, it has a set of system stores procedures in it. It won't have any user-created stored procedures until you create them.

To see the stored procedures in a database, open SSMSE, expand the Databases node, then expand a user database (if there is one) under that node. As an example, let's use the Northwind database that we've used in many of the previous columns. (Remember: Northwind isn't present by default; you need to install the SQL Server 2000 samples in order to have it. You can download the scripts to install the Northwind sample database at www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034.) Using SSMSE, expand the Northwind database node, then the Programmability node and the Stored Procedures node. If you expand the System Stored Procedures node, you'll see a surprisingly long list of system store procedures. You'll see this same set of system stored procedures in every database. Notice that they all begin with the prefix sp_.

SQL Server Express uses these system stored procedures for many of its own functions. However, you can certainly execute them as well. For example, you can open Query Editor and execute the sp_tables stored procedure by running the command

EXEC sp_tables

This system stored procedure returns a list of all the tables in the current database. If you execute the sp_stored_procedures stored procedure using the command

EXEC sp_stored_procedures

you'll get a list of all of the stored procedures in the current database.

From "Inside Stored Procedure Templates," you might recall that stored procedures can accept parameters and wonder how you can find out what those parameters are. The answer is, you can use the sp_help system stored procedure. Pass sp_help the name of the stored procedure whose parameters you want to discover. For example, use the command

EXEC sp_help sp_stored_procedures

to get all the parameters that can be used with the sp_stored_procedures stored procedure.