If you've ever done any type of programming, even just put together some simple DOS batch files, then you should be familiar with the concept of grouping statements together to perform a task or set of multiple tasks. This is the fundamental purpose of a SQL Server stored procedure, and it is a very powerful feature of the database platform. This article is designed to give you a very general introduction to stored procedures (a.k.a. "stored procs", "procs", or "SPs" to name a few) so that you can start putting them to work for you. Be sure to check out the discussion forum for this article to post your questions, ideas, and feedback, and see what others in the SQL Server Community have to say.

Creating and Modifying a Stored Procedure

There are a couple of ways that you can create a stored procedure. The simplest way I find to do so is to navigate through the enterprise manager to the particular database within which you want the sp to reside, right-click on the "Stored Procedures" caption (figure 1), then click "New Stored Procedure...". A form will open up with a somewhat generic and unhelpful T-SQL "Create Procedure" statement (figure 2).

Unless you are well versed in T-SQL, or are creating a very simple stored procedure with just a couple of statements, I recommend that you first test your work in the query analyzer client tool. Once you have your code working there as you expect it to, you can cut and paste it into the text area of the form. The opposite works well too - just use the CREATE PROCEUDRE syntax in the query analyzer to save your statements as an SP in the current database. A button at the bottom left of the form (figure 2) allows you to perform one last check of the T-SQL syntax before you save it. Keep in mind that this will not check to see if your statements perform as expected, just that they can be parsed correctly. The permissions button at the top gives you easy access to set permissions for the SP, but only after you save it (this has been my experience with a typical installation). Before you hit the OK button, give your stored procedure a name by editing the CREATE PROCEDURE statement and replace the [OWNER].[PROCEDURE NAME] text with a name that is somewhat descriptive of the purpose of the procedure.

You can edit your stored procedure just as easily as you created it. Double-click in the right-hand pane on the name of the procedure you wish to edit, and an edit form pops up similar to the one in figure 2. The difference is that you can now set permissions, and no longer save your SP as a template. Both of these subjects are beyond the scope of this article, but you can find all the information you need by performing a search of our site.

It is possible to edit your stored procedures directly from within the query analyzer. If you are running SQL Server 2000 tools, you can take advantage of a dramatically improved user interface. When you open up QA (Query Analyzer) and connect to a server, hit the F8 function key to bring up the object browser window, if it is not already displayed. Hitting F8 while the object browser is open will remove it from the display. Navigate to your particular database, right-click on the SP you want to edit, and select the "Script Object to New Window As" option (figure 3).

A sub-menu will pop up that gives you several options regarding what you can do with your procedure. The second option "Alter" will script out the SP you have selected using the ALTER PROCEDURE syntax. In a separate article, I'll review some of the ways you can use the object browser with other objects in the database to save a considerable amount of time during administration and development tasks.

Basic Elements of a Stored Procedure

It is possible to create a stored procedure with absolutely nothing in it, but what's the point? To do something useful, you need to throw together some T-SQL statements, toss in a few variables, and top it off with a parameter or two (or more). These are the basic elements of a stored procedure, and can be combined to perform tasks ranging from the simplest SELECT statement to the most insanely complex business rule validations.

Statements are the bulk and backbone of a stored procedure. They include the standard SQL syntax statements that you should be familiar with ("Select * from Table1", "Delete from Table2 Where…"), as well as "Control of Flow" statements (If…Else, While…., etc), and other SQL Server specific statements that allow you to control server settings, create and modify database objects, and perform certain diagnostic routines.

Variables in stored procedures serve the same purpose as variables in any programming language - they hold a temporary value in memory, used for the duration of the stored procedure. You declare a variable in an SP by using the "@" symbol, and it must also be assigned a data type. For example: DECLARE @strFirstName varchar(50) will declare a variable that can hold up to 50 characters. Within the stored procedure, you can use the SELCT or SET statements to assign a value to the variable. Both of these statements set the contents of the variable "@strFirstName" equal to the string value of "Bob":

Parameters are variables that are used to pass data elements in to and out of stored procedures. As you might expect, INPUT parameters are used to pass information to an SP, and OUTPUT parameters are used to retrieve results from the execution of an SP. By default, every parameter is an INPUT parameter, so if you need an OUTPUT parameter, you must declare it as such using this simple syntax: @contactID int OUTPUT. When you execute a procedure, you will need to pass to it a value for each INPUT and OUTPUT parameter included in your SP. The exception to this rule is when you declare your parameters with DEFAULT values, like this: @orderType int = 12. This will set the @orderType variable to a default of "12", unless the calling application passes a value to the SP that will overwrite it. You can do this with both INPUT and OUTPUT parameters.

Types of Stored Procedures

There are three types:

• User-Defined SPs
• SQL Server System SPs
• Extended SPs

A stored procedure you create is referred to as "user-defined". System stored procedures usually begin with "sp_" and most of them live in the Master database. They handle much of what is considered to be the nuts-and-bolts of SQL Server administration, and are written in T-SQL just like user-defined SPs. I recommend you script a few of them out for review to get a feel for what you can do with stored procedures; they can be great learning examples, but please be careful not to alter them. Many vital functions of SQL Server rely on these SPs to remain intact! Extended SPs will usually begin with "xp_" or "sp_" and also live in the Master database. They are not written in T-SQL; they are in fact compiled DLLs which open up a world of functionality to the SQL Server environment. Again, this subject is out of the scope of this article, but you should be aware that you can do some very powerful things with Extended SPs.

And Finally… Uses of Stored Procedures

Stored procedures are perfect for automating routine tasks that usually accompany SQL Server Administration. If you have a series of T-SQL Statements that you are constantly typing over and over, or even just loading and executing from QA, then why not put them in an SP and save yourself some keystrokes?

SPs are great for abstracting and consolidating SQL processes that are best done at the data layer, not within an application. The less your developers have to worry about, the better. You can create a procedure that enables an application to obtain a record set based on parameters it supplies to the SP, essentially creating a dynamic query. Stored procedures can execute batches of T-SQL statements, and return multiple result sets as well.

I hope that this has helped give you a jump start for working with Stored Procedures. There's a lot to them that is difficult to fully cover in a short article such as this, but the first step is just to get your hands dirty and test out some of your ideas! To that end, I encourage you to go to the discussion forum for this article, review what others have to offer there, and post your own questions or ideas.

If you have any feedback, I would be glad to hear from you - criticism included!  If you see that I have got anything wrong, please let me know so that I can correct it immediately.