What is a Stored Procedure?
The name for a batch of Transact-SQL or CLR code that is stored within
SQL Server and can be called directly by applications or within other
programming constructs.
Types of Stored Procedures
There are two main types of stored procedure -
- system stored procedures and
- user-defined stored procedures.
We also have extended stored procedures that can reside as either system or user-defined types. Extended stored procedures give functionality that is not necessarily contained within SQL Server, like allowing DOS commands to run and working with e-mail. It is also possible to create your own extended stored procedures.
System Stored Procedures
Although there is an engine that runs much of SQL Server, a number of stored procedures surround this. These are called while working with Enterprise Manager, or through a query tool, such as QueryAnalyzer. These procedures are installed with SQL Server, and hence, we don't have to create them.
Every time we add or modify a table, make a backup plan, or perform any
other administrative function from within Enterprise Manager, we
actually call a stored procedure specifically written to complete the
desired action. These stored procedures are known as system stored
procedures, which are functions that enhance the basic functionality of
SQL Server itself, either by extending the functionality of an existing
system stored procedure or by creating new functionality that enhances
the basics of what is already there.
For example, the sp_who system stored procedure will list connections to SQL Server, including all the system running processes.
System stored procedures are prefixed by sp_, so it is not advisable to
use sp_ for any of the stored procedures that we create, unless they
form a part of our SQL Server installation. Creating a stored procedure
prefixed with sp_ and placing it in the master database will make it
available to any database without the need to prefix the stored
procedure with the name of the database.
User Stored Procedures
A user stored procedure is any program that is stored and compiled within SQL Server (but not in the master database).
User stored procedures can be categorized into three distinct types -
- user stored procedures,
- triggers, and
- user defined functions.
Each of these types has its features, uses, limitations, and differences.
User defined stored procedures provide an interface to a set of
processing without the need for the end user to know the structure of
the table, the business logic of the organization, or the mathematics
involved, to produce the end result. They also provide a secure method,
which along with other security measures, can result in a database where
data is protected from malicious or accidental modifications.
A trigger is a stored procedure which fires when a specified table
action takes place. You are also limited to certain code that you can
place in a trigger, as you should not return any data from a trigger.
A user defined function is a stored procedure which can take parameters,
but only return one item of information, either a scalar value or a
table of data.
Creating Stored Procedures
The creation process depends on what we want it to do - we can either
build the whole procedure immediately, or build the T-SQL first (for
more complex solutions), check its performance, and finally, place it in
a stored procedure. Both ways require us to wrap the T-SQL code with
the same CREATE PROCEDURE command.
At the time of creation, SQL Server takes our code and parses it for any
syntactical errors. Column names and variables are checked for
existence at compilation. Even if they don't exist, any temporary table
created within the stored procedure will also pass the compilation
stage. This is known as deferred name resolution. It can be an asset or a
drawback, as we can create temporary table that exist for the lifetime
of the stored procedure execution only, which is desirable, but if we
define a temporary table and get something wrong later on, such as a
column name, then the compilation will not pick up this error.
While executing the CREATE PROCEDURE statement, errors will be reported.
Once it is compiled, the details of the stored procedure are stored in
three system table
in the concerned database:
in the concerned database:
sysobjects
This table contains a row for each object that is created within the database. If you want to track an object in the database, you can use this as the main root. Apart from the object name and type, this table also keeps track of the object's owner and time of creation. This can be useful in cross-checking the contents of our source control system with our database.
sysdepends
This table stores dependency information about objects. For example, when a stored procedure, view, or trigger is created, there can be references to other table, views, or procedures within it. These references are known as dependencies. If one of the dependent objects alters, we may need to recompile the stored procedure. For example, if we alter an index on a table, we should recompile every dependent object.
syscomments
This holds the original SQL definition statements of the stored procedure. It also holds details of views, rules, defaults, triggers, CHECK constraints, and DEFAULT constraints.
It is possible to interrogate these data. By looking for rows with a P
in the sysobjects table, we can obtain all the stored procedures. From
that, we can find out when the stored procedure was created, and also
move to the syscomments table by using the ID to retrieve detailed
information about the procedure itself, like, if it is encrypted or not.
These table are safe only for interrogation, although details within
them can always change between SQL Server releases. One of the best
methods to ensure that the code stored in your source control system
matches that within your database is to check if the dates and times
match or are within a few seconds of each other.
Why Use Stored Procedures?
By using stored procedures, we can reduce the time a process can take, as stored procedures are compiled.
Another gain from using stored procedures is that they are much simpler
to maintain, as compared to raw T-SQL code. Since the stored procedure
is held centrally within the database, any required bug fix, upgrade, or
modification can be completed centrally, thus reducing the downtime. If
the code is embedded within a program, as in VB.NET, we have to not
only change the code in one central place but also distribute it to
every client running the software. Modification of web sites also brings
in its own set of problems, and rolling out a new ASP.NET client can
prove problematic. This can be settled by using stored procedures.
The code in a stored procedure is executed in a single batch of work.
This means that it is not necessary to include a GO command while
writing code for procedures. SQL Server will take any of those
statements and implicitly execute them, as a batch.
Perhaps, the greatest reason to create a stored procedure rather than use inline T-SQL, is security.
