Microsoft
SQL Server provides the
stored procedure mechanism to simplify the database development process by grouping
Transact-SQL statements into manageable blocks.
Benefits of Stored Procedures
Why should you use stored procedures? Let's take a look at the key benefits of this technology:
- Precompiled execution. SQL Server compiles each stored
procedure once and then reutilizes the execution plan. This results in
tremendous performance boosts when stored procedures are called
repeatedly.
- Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
- Efficient reuse of code and programming abstraction. Stored
procedures can be used by multiple users and client programs. If you
utilize them in a planned manner, you'll find the development cycle
takes less time.
- Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.
Stored procedures are very similar to user-defined functions, but there are subtle differences. For more information, read
Comparing Stored Procedures and User-Defined Functions.
Structure
Stored procedures are extremely similar to the constructs seen in other
programming languages. They accept data in the form of input parameters
that are specified at execution time. These input parameters (if
implemented) are utilized in the execution of a series of statements
that produce some result. This result is returned to the calling
environment through the use of a
recordset,
output parameters and a return code. That may sound like a mouthful,
but you'll find that stored procedures are actually quite simple.
Example
Let's take a look at a practical example. Assume we have the table
shown at the bottom of this page, named Inventory.
This information is updated in real-time and warehouse managers are
constantly checking the levels of products stored at their warehouse
and available for shipment. In the past, each manager would run queries
similar to the following:
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = 'FL'
This resulted in very inefficient performance at the SQL
Server. Each time a warehouse manager executed the query, the database
server was forced to recompile the query and execute it from scratch.
It also required the warehouse manager to have knowledge of SQL and
appropriate permissions to access the table information.
We can simplify this process through the use of a stored
procedure. Let's create a procedure called sp_GetInventory that
retrieves the inventory levels for a given warehouse. Here's the SQL
code:
CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location
Our Florida warehouse manager can then access inventory levels by issuing the command
EXECUTE sp_GetInventory 'FL'
The New York warehouse manager can use the same stored procedure to access that area's inventory.
EXECUTE sp_GetInventory 'NY'
Granted, this is a simple example, but the benefits of
abstraction can be seen here. The warehouse manager does not need to
understand SQL or the inner workings of the procedure. From a
performance perspective, the stored procedure will work wonders. The
SQL Sever creates an execution plan once and then reutilizes it by
plugging in the appropriate parameters at execution time.
Now that you've learned the benefits of stored procedures, get
out there and use them! Try a few examples and measure the performance
enhancements achieved -- you'll be amazed!
Inventory Table
| ID | Product | Warehouse | Quantity |
| 142 | Green beans | NY | 100 |
| 214 | Peas | FL | 200 |
| 825 | Corn | NY | 140 |
| 512 | Lima beans | NY | 180 |
| 491 | Tomatoes | FL | 80 |
| 379 | Watermelon | FL | 85 |
Suggested Reading