SQL 2005+

SQL Server Stored Procedures

The twenty-third part of the SQL Server Programming Fundamentals tutorial describes stored procedures. A stored procedure allows you to create a script, or batch, containing multiple commands to be executed, potentially being controlled by parameters.

What is a Stored Procedure?

A SQL Server stored procedure is a stored script or batch containing one or more Transact-SQL (T-SQL) statements that can be executed with a single command. The script can include any T-SQL statements, including those that query the database or create, modify or delete data in tables, as well as flow-of-control commands, mathematical operators and many predefined functions.

Stored procedures are often used to provide a level of abstraction for a database, hiding implementation details. Rather than permitting users from reading or updating information directly, you can create stored procedures that carefully control these operations and ensure that business rules are not broken. For example, in a banking system you may need to perform several updates to transfer money between accounts, ensuring that one account is credited, a second is debited and a third table is updated in a single transaction to record the transfer correctly and safely. By creating a stored procedure that includes all of these commands, a transfer can be executed in a single named call and in a consistent manner.

In the account transfer example, the transfer requires the knowledge of information such as a source and destination account and the amount of money to be moved. To allow such information to be used by a stored procedure, you can define parameters that accept values of any of the SQL Server data types. These are known as input parameters, as they are passed into the stored procedure and can be used freely within. The use of stored procedure parameters can be helpful when trying to avoid SQL injection attacks.

Stored procedures also permit the use of output parameters so that information can be returned to the caller of the batch. These allow information to be returned from the stored procedure in addition to the single return value that usually reports the status of the procedure. Of course, you can also query tables from within a stored procedure and return the results of the query as a recordset.

Some developers prefer not to use stored procedures in their applications. There are many reasons for this, one important one being the introduction of object-relational mapping (ORM) frameworks that use dynamically generate SQL statements to convert between data in a database and objects in an object-oriented programming language. There are many advantages to such an approach and a great deal of flexibility but also some disadvantages. Key to the disadvantages is that ORM frameworks generally require direct access to the database tables. This removes a level of abstraction and also limits security options. With stored procedures, you can restrict direct table access and force the used of the procedures, ensuring business rules are not overridden. Secondly, using a stored procedure only requires the sending of the name and parameters to the server. If a client performs complex functionality that requires many hundreds or thousands of statements to be executed, each of these may be sent over the network, generating unnecessary traffic. Some ORM tools recognise this and can call stored procedures, combining the use of the two technologies.

In this article we will create some stored procedures and examine the use of parameters. The examples will be reasonably simple and concentrate on the overall structure of a script rather than the fine detail. Over the rest of the tutorial we will be examining many more T-SQL commands including operators, functions, loops and conditional processing statements. Each of these can be used within stored procedures to create powerful scripts.

Creating Stored Procedures

In this article we will examine the creation of stored procedures against the JoBS tutorial database. This is the database that has been created throughout the course of the tutorial. If you do not have an up to date version of the database, you can use the link at the top of this article to download a script. Execute the script to generate the database and populate it with sample data.