T-SQL 101: Stored Procedures

A term you'll often encounter when talking with DBAs or reading SQL Server documentation or literature is stored procedures. A stored procedure is simply a compiled database object that contains one or more T-SQL statements. Although SQL Server has many system-provided stored procedures, you can create your own.

To review and learn more about the complete T-SQL 101 lesson series, visit "T-SQL 101 Series Isn't Just for Novices." 

Creating your own stored procedures offers several advantages:

The Prerequisites

Before I show you how to create and execute stored procedures, make sure your MyDB database contains the following tables so that you can run the sample code in this lesson:

If you haven't created these database objects, you'll find the code in the 100844.zip file. To download this file, click the "Download the Code" hotlink at the top of the page.

How to Create Stored Procedures

Before you create a stored procedure, you need to decide what you want your stored procedure to do. In previous lessons, a series of T-SQL statements were used to populate the Employee, Movie, and MovieReview tables, so I'll show you how to create three simple stored procedures that you can use to insert new records into these tables. I'll also add some code to prevent duplicate records from being inserted into the tables.

To create a stored procedure, you use the CREATE PROCEDURE command. This command's basic syntax is

CREATE PROCEDURE  SchemaName.ProcedureName(  @Parameter1 datatype [OUTPUT],  @Parameter2 datatype [OUTPUT])AS  SQL Statement 1  SQL Statement 2

The first portion of the CREATE PROCEDURE statement is where you specify the name of the stored procedure (ProcedureName) and optionally the schema to which it will belong (SchemaName). You can read more about schemas in "T-SQL 101, Lesson 6".

The second portion is where you specify optional input and output parameters. These parameters are defined within the parentheses and are separated by commas. Each parameter is prefixed with the @ symbol and must have a data type specified. Each parameter specified will accept an input value, but if you want to return a value back to the calling EXECUTE statement (i.e., the command that caused the stored procedure to run) you must specify the OUTPUT argument for that parameter. You can have as many input and output parameters as needed.

The third portion of the CREATE PROCEDURE statement begins with the AS keyword. Often referred to as the main body of the stored procedure, this is where you specify the T-SQL statements that you want to execute. You can have as many T-SQL statements as needed. (For more information about the CREATE PROCEDURE's syntax, go to http://msdn.microsoft.com/en-us/library/ms187926(SQL.90).aspx.)

Let's look at an example of a CREATE PROCEDURE command. Suppose that three new employees have joined the fictitious company in which employees review movies discussed in previous lessons. Listing 1 shows the code that creates a stored procedure named InsertEmployee, which you can use to insert records into the Employee table.

Because the Employee table has four columns (i.e., FirstName, LastName, Salary, and HireDate), the procedure has four input parameters, as callout A shows. When you execute this stored procedure, you'll pass in the employee's first name, last name, current salary, and hire date using the @FirstName, @LastName, @Salary, and @HireDate parameters, respectively.

The InsertEmployee stored procedure's main body has been broken down into three sections. Each section has a comment that provides a brief description of what that section of code accomplishes. Comments begin with a double hyphen (--). They're for documentation purposes and not execution. (For more information about comments, go to http://msdn.microsoft.com/en-us/library/ms181627(SQL.90).aspx.)

In the first section, a local variable is defined and initialized. Local variables hold a single data value of a specific data type. (For more information about local variables, go to http://msdn.microsoft.com/en-us/library/ms187953(SQL.90).aspx.) Although local variables aren't required in stored procedures, InsertEmployee uses a local variable named @count in the second section.

After @count is defined as having an integer value, it's initialized to 0. Although SQL Server doesn't require you to initialize variables, it's a good habit to get into, especially when you'll be joining, or concatenating, those variables with strings. On several occasions I've helped developers troubleshoot their code because they couldn't determine why there was no output being generated. As it turns out, they were taking an uninitialized variable and appending it to a string. The variable contained a NULL value, which by default causes SQL Server to nullify the entire string.

The second section contains the code that prevents duplicate records from being inserted into the Employee table. A SELECT statement counts the number of Employee table records whose FirstName and LastName fields match the @FirstName and @LastName input parameters. The @count variable stores that number.

The third section of the stored procedure starts off with an IF…ELSE statement. If the value stored in the @count variable is 0, then the statements inside the BEGIN…END block will execute. The BEGIN and END keywords are only necessary if you have more than one statement to execute. In this case, there are two statements. The first statement inserts the record into the Employee table. The second statement prints a message stating that the record was inserted.

If the number stored inside the @count variable is anything other than 0, then the statement following the ELSE keyword will execute. Because there's only one statement following the ELSE keyword, you don't need to use a BEGIN…END block. You can read more about IF...ELSE statements at http://msdn.microsoft.com/en-us/library/ms182717(SQL.90).aspx.

The last line of code in the InsertEmployee stored procedure is the GO command. It's a good idea to follow each CREATE PROCEDURE statement with the GO command. This command tells SQL Server that there are no more statements for the stored procedure and lets you create multiple stored procedures in one script.

After you've written the stored procedure, you need to add it to the database. To do so, simply run the code in the query editor in SQL Server 2005’s SQL Server Management Studio (SSMS) or SQL Server 2000’s Query Analyzer.

How to Execute Stored Procedures

To execute a stored procedure, you need to use the EXECUTE command followed by the name of the stored procedure and the necessary input parameters. In this case, each input parameter needs to be followed by an equal sign and the value being passed in. For example, to add a record to the Employee table with the InsertEmployee stored procedure, you'd use a command such as

EXECUTE InsertEmployee  @FirstName = 'Graham',  @LastName = 'Oatley',  @Salary =  99000,  @HireDate = '2008-01-01'

The results would look like

(1 row(s) affected)Employee record inserted

If you try running the same command again, you'll see the results of the record-duplication check:

Employee record already exists

Next, run the code in Listing 2 to add two more employee records to the Employee table.

To confirm that the stored procedure worked as expected and actually inserted all three employee records, execute the statement

SELECT *  FROM EmployeeWHERE HireDate >= '2008'

Figure 1 shows the results.

Figure 1: Confirmation that the InsertEmployee stored procedure worked

Note that there are five columns even though the stored procedure adds values to only four columns. As I explained in "T-SQL 101, Lesson 2", the first column is an identity column. SQL Server automatically adds that column's values. The value is incremented with each record being inserted. Because the Employee table had eight existing records, the three new records have the EmployeeID values of 9, 10, and 11.

Now let's suppose that the three new employees wanted in on reviewing the movies they watched, so they diligently submitted their movie reviews. Some of those reviews were for movies not in the Movie table, so records need to be added to both the Movie table and MovieReview tables. To insert data into these tables, you can use the InsertMovie and InsertMovieReview stored procedures. These stored procedures are very similar to the InsertEmployee procedure, so I won't discuss how they work. You can find the code for these stored procedures in the InsertMovie.sql and InsertMovieReview.sql files, which are in the 100844.zip file at the top of the page.

After you execute the code to add the InsertMovie and InsertMovieReview to the database, run the code in the CodeToAddMoreMovies.sql file to add the three new movies to the Movie table. Then execute the code in the CodeToAddNewMovieReviews.sql file to insert the new employees' movie reviews in the MovieReview table.

CodeToAddNewMovies.sql and CodeToAddNewMovieReviews.sql are also in the 100844.zip file at the top of the page.

How to Use Stored Procedures to Produce Reports

So far the InsertEmployee, InsertMovie, and InsertMovieReview stored procedures have been used to get data into the database. The code in Listing 3 creates a stored procedure, ShowMovieReviews, that you can use to get data out of the database in the form of a report.

The report lists each movie in a particular genre and provides that movie's average rating, which is calculated from the ratings in the applicable movie reviews. ShowMovieReviews takes one input parameter: @Genre. For example, to generate a report that shows all the thriller movies and their average ratings, you'd execute the code

EXECUTE ShowMovieReviews  @Genre = 'Thriller'

Figure 2 shows the results.

Figure 2: The ShowMovieReviews stored procedure's results for the thriller genre

Similarly, to generate a report that shows all the sci-fi movies and their average ratings, you'd run the command

EXECUTE ShowMovieReviews  @Genre = 'Sci-Fi'

Figure 3 shows the results.

Figure 3: The ShowMovieReviews stored procedure's results for the sci-fi genre

The Journey's End

It has been a long journey to get to this final lesson. In this series, you learned how to create tables and views, how to insert, update, and delete records in tables, and how to summarize and aggregate data. You learned about the difference between inner and outer joins and the various system functions that you can use to manipulate data in queries. In this final lesson, you learned how to tuck all those useful T-SQL commands inside a wonderful container known as a stored procedure.

I've had a lot of fun with this series (you might have noticed my corny jokes strewn about). Believe it or not, I actually learned a lot. I keep a copy of "T-SQL-101, Lesson 5: How to Join Tables" on my desk at work for those sleepy days when I just can't remember the difference between an INNER JOIN and a RIGHT OUTER JOIN.

I truly hope you found this series useful. My goal was to introduce novices to the basics of the T-SQL language while encouraging them to dig deeper on their own. If you've been reading along, doing the exercises, and are much more comfortable with T-SQL than when you first started, then I have only one thing further to say: Congratulations, you just graduated!