SQL Server - Stored Procedures

Note that this tutorial uses the 2000 edition of SQL Server (SQL Server 2000).

For the latest version, see SQL Server tutorial.

Stored procedures are a powerful part of SQL Server. They can assist programmers and administrators greatly in working with the database configuration and its data.

A stored procedure is a precompiled group of Transact-SQL statements, and is saved to the database (under the "Stored Procedures" node). Programmers and administrators can execute stored procedures either from the Query Analyzer or from within an application as required.

Transact-SQL, which is based on SQL (Structured Query Language), is the programming language used to interface between applications and their databases. Transact-SQL is a relatively easy language to learn and I highly recommend becoming familiar with it.

Benefits of Stored Procedures

Here are some key benefits in using stored procedures:

BenefitExplanation of benefit
Modular programmingYou can write a stored procedure once, then call it from multiple places in your application.
PerformanceStored procedures provide faster code execution and reduce network traffic.
  • Faster execution: Stored procedures are parsed and optimized as soon as they are created and the stored procedure is stored in memory. This means that it will execute a lot faster than sending many lines of SQL code from your application to the SQL Server. Doing that requires SQL Server to compile and optimze your SQL code every time it runs.
  • Reduced network traffic: If you send many lines of SQL code over the network to your SQL Server, this will impact on network performance. This is especially true if you have hundreds of lines of SQL code and/or you have lots of activity on your application. Running the code on the SQL Server (as a stored procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied and the results of any query.
SecurityUsers can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way.

Creating a Stored Procedure

You create stored procedures in the Query Analyzer using the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure.

The following code creates a stored procedure called "MyStoredProcedure":

Once you run this code in the Query Analyzer, the stored procedure is created and appears under the "Stored Procedures" node.

Modifying a Stored Procedure

If you need to modify an existing stored procedure, you simply replace the CREATE with ALTER.

Running a Stored Procedure

You can run a stored procedure by using EXECUTE or EXEC. For example, to run the above stored procedure, type the following:

If the stored procedure has spaces in its name, enclose it between double quotes:

If your stored procedure accepts any parameters, they are placed after the procedure name:

The following screenshot shows the results of running a stored procedure called "SalesByCategory" and passing it a parameter of "Beverages":

Results of stored procedure

Parameters

A parameter is a value that your stored procedure uses in order to perform it's task. When you write a stored procedure, you can specify the parameters that need to be supplied from the user. For example, if you write a stored procedure to select the address details about an individual, your stored procedure needs to know which individual to select. In this case, the user can provide an IndividualId or UserId to tell the stored procedure which individual to look up.

System Stored Procedures

SQL Server includes a large number of system stored procedures to assist in database administration tasks. Many of the tasks you can perform via Enterprise Manager can be done via a system stored procedure. For example, some of the things you can do with system stored procedures include:

Related