What is a Stored Procedure?

If a repetitive T-SQL task has to be executed within an application, then the best repository for it is a program called a stored procedure, stored in SQL Server. Storing the code inside the SQL Server object gives us many advantages, like:

• Security due to encryption

• Performance gains due to compilation

• Being able to hold the code in a central repository:

  • Altering the code in SQL Server without replicating in several different programs

  • Being able to keep statistics on the code to keep it optimized

• Reduction in the amount of data passed over a network by keeping the code on the server

• Hiding the raw data by allowing only stored procedures to gain access to the data

You may have executed some ad-hoc queries for tasks like inserting data, querying information in other systems, or creating new database objects such as tables. All these tasks can be placed within a stored procedure, so that any developer can run the same code without having to recreate the T-SQL commands. Also, generalising the code for all values makes it generic and reusable.

Stored procedures are more than just tools for performing repetitive tasks. They can ensure the integrity of data (through standalone stored procedures or triggers), behave as subroutines for performing part of a task that can be called from several routines (known as user-defined functions), or even act as SQL Server routines for everyone to use.

There are two main types of stored procedure – system stored procedures and user-defined stored procedures. We will see how these differ, in the next section. 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. This book doesn't cover building extended stored procedures, as these are written using C++ libraries.

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, but if you do not want to see those system processes, you can write a new sp_whouser system stored procedure based on sp_who by taking sp_who as a basis, and create the new procedure from that.

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.

Lets clarify this with an example. If we take the sp_who stored procedure, call it sp_mywho, store it in the master database, and move to another database such as northwind, we can still execute sp_mywho, rather than having to specify the procedure in the fully qualified manner as master.dbo.sp_mywho.

Although SQL Server comes with many useful and relevant system stored procedures built in, there is always room for improvement. We will discuss how we can harness the full capablities of SQL Server by inspecting these and improve the usability of SQL Server by creating our own, in Chapter 5.

Important

If you want to extend the functionality of an existing system stored procedure, do not alter the existing procedure– create your own version instead.

If we create our own system stored procedure then we will need to place them within all installations of SQL Server (an instance of SQL Server is a separate installation, and hence a separate entity) in our organization, to ensure consistency. There are several reasons for doing this. For example, it is possible to create development, testing, and production installations as completely separate entities. We may find the development and test instance on one machine, and the production instance on a different one.

We can see that there are three instances defined in the following screenshot – a Development instance, a Live instance, and a Local instance. The Development and Local instances reside on the same computer, while the Live instance is on a remote computer. Therefore, each instance would have its own stored procedures to perform the administrative tasks:

System stored procedures reside in the master database or the model database for each instance installed. Every stored procedure residing in the model database will be placed into any database that we create within our SQL Server instance, by default.

Important

Each instance of SQL Server, even if there are several instances on the same machine, would have its own set of stored procedures.

When we create a new database, it is based on the model database. Therefore, any new system or user stored procedures added to the model database will also be placed into each new database that we create.

It is rare to create system procedures that are specific to one database. Generally, they are implemented as user stored procedures. Now, let's take a look at them and see how these differ from system stored procedures.

User Stored Procedures

A user stored procedure is any program that is stored and compiled within SQL Server (but not in the master database) and prefixed with sp_. 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.

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

2. 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. We will see more of this in Chapter 7.

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