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.

Creating your own stored procedures offers several advantages:

• You can avoid having to store all your T-SQL code in files. Stored procedures are stored in the database itself, so you never have to search through files to find the code you want to use.
• You can execute a stored procedure as often as you like from any machine that can connect to the database server.
• If you have a report that needs to be run frequently, you can create a stored procedure that produces the report. Anyone who has access to the database and permission to execute the stored procedure will be able to produce the report at will. They don't have to understand the T-SQL statements in the stored procedure. All they have to know is how to execute the stored procedure.
• You can enforce database security through stored procedures. You can grant users permission to execute a stored procedure but not permission to access to the underlying tables.
• Although creating applications is beyond the scope of this lesson, centralizing code in a stored procedure lets you reduce the amount of redundant code in your applications and insulate the applications from the effects of database schema changes.

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:

• The Employee table created in Lesson 3
• The Movie table created in Lesson 5
• The Genre table created in Lesson 5
• The revised MovieReview table created in Lesson 6 (and not the original MovieReview table created in Lesson 4)

If you haven't created these database objects, you'll find the code in the 100844.zip file. To download this file, click the 100844.zip 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.)