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 tables, 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 tables 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 tables 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 tables in the concerned database:

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.

This table stores dependency information about objects. For example, when a stored procedure, view, or trigger is created, there can be references to other tables, 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.

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 tables 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.

How much would you like a toothbrush?