Stored procedures can offer performance gains when used instead of
regular queries. This article will start with the basics and give you
the complete overview on stored procedures and how to use them. Have
you ever thought what happens when your queries get to the database?.
They are actually compiled each time you query and then executed, but
this is not the ideal approach. A much better option would be to have
the database server compile the query, store it in a compiled format
and run it on request, without having to recompile it each and every
time. This is where the concept of stored procedures comes into play.
In this article we are going to learn how stored procedures work, what
the benefits of using stored procedures are, and also how to create
simple as well as complex stored procedures that accept and return parameters.
In this article I will be focusing on using Microsoft SQL Server, however
many of the principles in this article can also apply to other RDBMS's
such as Oracle.
Near the end of this article we will learn how to make changes to a
stored procedure and drop existing stored procedures. By the end of
this article you will be fully prepared to start using basic stored
procedures in your applications
What are stored procedures
Stored procedures are collections of SQL statements and control-of-flow
language. Stored procedures differ from ordinary SQL statements and
from batches of SQL statements in that they are pre-compiled. The first
time you run a procedure, SQL Server's query processor analyzes it and
prepares an execution plan that is ultimately stored in a system table.
The subsequent execution of the procedure is according to the stored
plan. Since most of the query processing work has already been performed,
stored procedures execute almost instantaneously.
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/or a return code.
Stored procedures in SQL Server are similar to procedures in other
programming languages in that they can:
- Accept input parameters and return multiple values in the form of
output parameters to the calling procedure or batch.
- Contain programming statements that perform operations in the database,
including calling other procedures.
- Return a status value to a calling procedure or batch to indicate
success or failure (and the reason for the failure). Benefits
- Precompiled: 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
- Efficient reuse of code: Multiple users and client programs can
use stored procedures. 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.
- They allow faster execution: If the operation requires a large amount
of Transact-SQL code or is performed repetitively, stored procedures
can be faster than batches of Transact-SQL code. They are parsed and
optimized when they are created, and an in-memory version of the procedure
can be used after the procedure is executed the first time. Transact-SQL
statements repeatedly sent from the client each time they run are
compiled and optimized every time SQL Server executes them.
- Another benefit is that you can execute a stored procedure on either
a local or remote SQL Server. This enables you to run processes on
other machines and work with information across servers, not just
- An application program written in a language, such as C or Visual
Basic, can also execute stored procedures, providing an optimum solution
between the client-side software and SQL Server.
Defining Stored Procedures
You use the
statement to create a stored procedure. The maximum stored procedure
name length is thirty characters. The syntax that you use to define
a new procedure is as follows:
CREATE PROCEDURE [owner,] procedure_name [;number]
[@parameter_name datatype [=default] [OUTput]
[@parameter_name datatype [=default] [OUTput]
[FOR REPLICATION] | [WITH RECOMPILE] , ENCRYPTION
In this example, a simple procedure is created that contains a
SELECT statement to display
all rows of a table. After the procedure is created, its name is entered
on a line to execute the procedure.
Stored procedures can either be created by sending commands to SQL
Server through ADO, or they can be created in the Query Analyzer application,
which is the most popular way to do so.
Creating and Running a Stored Procedure
create procedure all_employees
as select * from employees
name department badge
Brat Smith Sales 1234
Karen Jones Sales 5514
( 2 row(s) affected)
When you submit a stored procedure to the system, SQL Server compiles
and verifies the routines within it. If any problems are found, the
procedure is rejected and you'll need to determine what the problem
is prior to re-submitting the routine. If your stored procedure references
another, as yet unimplemented stored procedure, you'll receive a warning
message, but the routine will still be installed.
If you leave the system with the stored procedure that you previously
referred to uninstalled, then the user will receive an error message
Stored procedures are treated like all other objects in the database.
They are therefore subject to all of the same naming conventions and
other limitations. For example, the name of a stored procedure cannot
contain spaces, and it can be accessed using the database convention.
Using Parameters with SP
Stored procedures are very powerful but to be most effective the procedure
must be somewhat dynamic, which enables you, the developer, to pass
in values to be considered during the functioning of the stored procedure.
Here are some general guidelines for using parameters with stored procedures:
- You can define one or more parameters in a procedure.
- You use parameters as named storage locations just like you would
use the parameters as variables in conventional programming languages,
such as C and Visual Basic.
- You precede the name of a parameter with an at symbol (@) to designate
it as a parameter.
- Parameter names are local to the procedure in which they're defined.
You can use parameters to pass information into a procedure from the
line that executes the parameter. You place the parameters after the
name of the procedure on a command line, with commas to separate the
list of parameters if there is more than one. You use system data types
to define the type of information to be expected as a parameter.
In example below, the procedure is defined with three input parameters.
The defined input parameters appear within the procedure in the position
of values in the
VALUE clause of an
INSERT statement. When the
procedure is executed, three literal values are passed into the
INSERT statement within the
procedure as a parameter list. A
SELECT statement is executed after the stored
procedure is executed to verify that a new row was added through the
Creating a Stored Procedure with Input Parameters
create procedure proc4 (@p1 char(15), @p2 char(20), @p3 int) as
insert into Workers
values (@p1, @p2, @p3)
select * from Workers
Name Department Badge
Brat Sales 3333
(1 row(s) affected).
Calling Stored Procedures from Your Application
On the application side, it can be quite cumbersome to have to specify
each value on every call to the stored procedure, even in cases where
the value is
In those cases, the calling application can use named arguments to pass
information to SQL Server and the stored procedure. For example, if
your stored procedure allows up to three different arguments, name,
address, and phone, you can call the routine as follows:
exec sp_routine @name="blah"
Displaying and Editing Procedures
You use the system procedure sp_helptext to list the definition of
a procedure, and sp_help to display control information about a procedure.
The system procedures sp_helptext and sp_help are used to list information
about other database objects, such as tables, rules, and defaults, as
well as stored procedures.
Making Changes and Dropping Stored Procedures
Two closely related tasks that you'll no doubt have to perform are
making changes to existing stored procedures and removing no longer
used stored procedures.
Changing an Existing Stored Procedure
Stored procedures cannot be modified in place, so you're forced to
first drop the procedure, then create it again. Unfortunately, there
that can be used to modify the contents of an existing procedure. These
stems largely from the query plan that is created and from the fact
that stored procedures are compiled after they are initiated.
Because the routines are compiled and the query plan relies on the
compiled information, SQL Server uses a binary version of the stored
procedure when it is executed. It would be difficult or impossible to
convert from the binary representation of the stored procedure back
to English to allow for edits. For this reason, it's imperative that
you maintain a copy of your stored procedures in a location other than
SQL Server. Although SQL Server can produce the code that was used to
create the stored procedure, you should always maintain a backup copy.
You can pull the text associated with a stored procedure by using the
sp_helptext system stored procedure. The syntax of sp_helptext is as
Removing Existing Stored Procedures
You use the
DROP PROCEDURE statement to drop a stored procedure
that you've created. Multiple procedures can be dropped with a single
DROP PROCEDURE statement by listing multiple procedures separated by
commas after the keywords DROP PROCEDURE in the syntax:
DROP PROCEDURE procedure_name_1, ...,procedure_name_n
Example of stored procedure
Let's assume that we have the following table 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
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
retrieves the inventory levels for a given warehouse. Here's the SQL
CREATE PROCEDURE sp_GetInventory
SELECT Product, Quantity
WHERE Warehouse = @location
Our Florida warehouse manager can then access inventory levels by issuing
EXECUTE sp_GetInventory 'FL'
The New York warehouse manager can use the same stored procedure to
access that area's inventory:
Granted, this is a simple example, but the benefits of abstraction
can be seen here. The warehouse manager does not need to understand
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.
Hopefully this article has shed some light on the basics and principles
of why you should use stored procedures instead of regular SQL queries.
If you're a Database administrator, then stored procedures are a great
way to hide complex programming logic from your developers. If you're
a developer who isn't too proficient in SQL, then allowing your database
administrator to create procedures that you can plug values into is
a great way to get the data you need without the performance hit of
an inexperienced user creating the TSQL code.