Anatomy of a Stored Procedure

We can describe a stored procedure in terms of

• Composition

• Functionality

• Syntax

Composition

Logically, a stored procedure consists of

• A header that defines the name of the stored procedure, the input and output parameters, and some miscellaneous processing options. You can think of it as an API (application programming interface) or declaration of the stored procedure.

• A body that contains one or more Transact-SQL statements to be executed at runtime.

Creating Stored Procedures

Let's look at the simplified syntax for implementing the core functionality of stored procedures:

 CREATE PROC[EDURE] procedure_name     [ {@parameter data_type} [= default] [OUTPUT] ] [,...n] AS     sql_statement [...n] 

The following is an example of a stored procedure:

  Create Procedure ap_Equipment_Get       @chvMake varchar(50)  as       Select *       from dbo.Equipment       where Make = @chvMake 

This Transact-SQL statement creates a stored procedure named ap_Equipment_Get with one input parameter. During execution, ap_Equipment_Get returns a result set containing all records from the Equipment table having a Make column equal to the input parameter.

Please, be patient and do not create the procedure in the Asset5 database yet. If you try to create a stored procedure that already exists in the database, SQL Server will report an error. You can reproduce such an error if you run the same statement for creating a stored procedure twice. For example:

 Msg 2714, Level 16, State 3, Procedure ap_Equipment_Get, Line 4 There is already an object named 'ap_Equipment_Get' in the database. 

As I have shown in Chapter 1, one way to change a stored procedure is to drop and re-create it. There are two ways to prevent the error just described. One way is to use an Alter Procedure statement to change the stored procedure. I will explain this technique in the next section. The traditional way to prevent this error is to delete a stored procedure (using the Drop Procedure statement) and then create it again:

 Drop Procedure ap_EquipmentByEqTypeID_Get go Create Procedure ap_EquipmentByEqTypeID_Get      @intEqTypeId int as      Select *      from dbo.Equipment      where EqTypeId = @intEqTypeId GO 

If you are not sure whether a stored procedure exists, you can write a piece of code to check for its existence. If you do not, SQL Server will report an error when you try to drop a stored procedure that does not exist. This code takes advantage of the fact that SQL Server records each database object in sys.objects system view. It also uses programming constructs I have not yet introduced in this book. For now, do not worry about the details. All will become clear later.

 if  exists (select *     from sys.objects     where object_id = object_id(N'[dbo].[ap_EquipmentByEqTypeID_Get]')     and type in (N'P', N'PC'}} DROP PROCEDURE [dbo].[ap_EquipmentByEqTypeID_Get] GO CREATE PROCEDURE [dbo].[ap_EquipmentByEqTypeID_Get]    @intEqTypeId [int] AS    Select *     from dbo.Equipment     where EqTypeId = @intEqTypeId GO 

Note 

Most of the stored procedures in this book already exist in the database. If you just try to create them, SQL Server will complain with error 2714. If you are sure that the code that you have typed is correct, you can drop the original stored procedure and put yours in its place. Or you can alter the original stored procedure and use your code instead.

It is much better to rename your stored procedure. All stored procedures in the Asset5 datahase start with the ap_ prefix. You could start yours, for example, with up_ (for user procedure).

I follow a similar practice when I create several versions of the same stored procedure to illustrate a point or a technique. I merely change the stored procedure's suffix by adding a version number (for instance, _1, _2, and so on).

Altering Stored Procedures

The other way to change a stored procedure is to use the Alter Procedure statement:

 Alter  Procedure   [dbo].[ap_Equipment_Get]       @chvMake varchar(50) as Select *       from dbo.Equipment       where Make = OchvMake go 

The syntax of this statement is identical to the syntax of the Create Procedure statement (except for the keyword). The main reason for using this statement is to avoid undesirable effects on permissions and dependent database objects. For more details about permissions, see Chapter 18.

The Alter Procedure statement preserves all aspects of the original stored procedure. The object identification number (id column) of the procedure from the sys.objects system view remains the same, and all references to the stored procedure are intact. Therefore, it is much better to use the Alter Procedure statement than to drop and re-create the procedure. For more details about the sys.objects table and the object identification number (id column), see "Storing Stored Procedures" in Appendix B.

Limits

When you are creating or changing a stored procedure, you should keep in mind the following limits:

• The name of the procedure is a standard Transact-SQL identifier. The maximum length of any identifier is 128 characters.

• Stored procedures may contain up to 2,100 input and output parameters.

• The body of the stored procedure consists of one or more Transact-SQL statements. The maximum size of the body of the stored procedure is 128MB.

Functionality

Stored procedures can be used to

• Return information to the caller

• Modify data in databases

• Implement business logic in data tier

• Control access to data

• Improve performance of the system

• Reduce network traffic

• Perform other actions and operations (such as process e-mail, execute operating system commands and processes, and manage other SQL Server objects)

There are four ways to receive information from a stored procedure:

• Returning result sets

• Using input and output parameters

• Using return values

• Global cursor

The first three will be explained in this chapter, while the fourth one will be skipped as not recommended.

Returning Result Sets

To obtain a result set from a stored procedure, insert a Transact-SQL statement that returns a result set into the body of the stored procedure. The simplest way is by using a Select statement, but you could also call another stored procedure.

It is also possible to return several result sets from one stored procedure. Such a stored procedure will simply contain several Select statements. You should note that some client data-access methods (such as ADO.NET) can access all result sets, but others will receive just the first one or possibly even report an error.

Using Input and Output Parameters

Let's add a new procedure to the Asset5 database:

 Create procedure dbo.ap_Eq!dByMakeModel_List      @chvMake varchar(50),      @chvModel varchar(50) as      select Eqld      from dbo.Equipment      where Make = @chvMake      and Model = @chvModel GO 

This is a very simple stored procedure. It uses two input parameters to receive the make and model, and returns identifiers of equipment that matches the specified make and model.

Physically, the stored procedure encapsulates just one Select statement. The header and body of the procedure are divided by the keyword As. The header of the stored procedure contains a list of parameters delimited with a comma (,) character. Each parameter is defined with an identifier and a data type. Parameter identifiers must begin with the at sign (@).

You can use the following statement to execute the stored procedure:

 Execute dbo.ap_Eq!dByMakeModel_List 'Toshiba', 'Portege 7020CT' 

The keyword Execute is followed by the name of the stored procedure. Since the stored procedure requires two parameters, they are provided in the form of a comma-delimited list. In this case they are strings, so they must be delimited with single quotation marks.

The keyword Execute is not needed if the stored procedure is executed in the first statement of a batch:

 dbo.ap_Eq!dByMakeModel_List 'Toshiba', 'Portege 7020CT' 

However, I recommend you use it. It is a good habit that leads to clean code. You can use its shorter version (Exec) to save keystrokes:

 Exec dbo.ap_Eq!dByMakeModel_List 'Toshiba', 'Portege 7020CT' 

In any case, the execution will return a result set containing just one value in one record:

 EquipmentId ----------- 1 (1 row(s) affected) 

Stored procedures can return output parameters to the caller. To illustrate, we will create a stored procedure similar to the previous one, but having one critical difference: This new stored procedure contains an additional parameter. The direction of the parameter is controlled by including the keyword Output after the data type:

 Create procedure dbo.ap_Eq!dByMakeModel_List_2      @chvMake varchar(50),      @chvModel varchar(50),      @intEqId int output as      select @intEqId = EquipmentId      from dbo.Equipment      where Make = @chvMake      and Model = @chvModel 

The Select statement does not return a result set, as the previous one did. Instead, it assigns an output parameter, @EqId, with the selected value.

Note 

This stored procedure is not perfect. It may seem correct at first glance, but there is a potential problem with it. More than one piece of equipment (that is, more than one record) could correspond to the criteria. I will address this issue in detail in the chapters to follow.

In this case, we require a more complicated batch of Transact-SQL statements to execute the stored procedure. We must define the variable that will receive the output value. The parameter must be followed by the Output keyword to indicate that a value for the parameter will be returned by the procedure. At the end of the batch, the result of the stored procedure is displayed using the Select statement:

      Declare @intEqId int      Execute dbo.ap_EqIdByMakeModel_List_2 'Toshiba',                                            'Portege 7020CT',                                             @intEqId OUTPUT      Select @intEqId 'Equipment Identifier' 

The batch returns the value of the variable as an output parameter:

 Equipment Identifier -------------------- 1 (1 row(s) affected) 

Note 

A typical error is to forget to mark parameters in Execute statements with Output. The stored procedure will he executed, hut the value of the variable will not be returned.

Using Return Values

An alternative way to send values from a stored procedure to the caller is to use a return value. Each stored procedure can end with a Return statement. The statement can be followed by an integer value that can be read by the caller. If the return value is not explicitly set, the server will return the default value, zero (0).

Because return values are limited to int data types, they are most often used to signal an error status or error code to the caller. We will examine this use later. First, let's explore its functionality in some unorthodox examples.

In the following example, the value returned by the procedure will be assigned to the local variable and finally returned to the caller:

 Create Procedure [dbo].[ap_EqIdByMakeModel_List_3]      @chvMake varchar(50),      @chvModel varchar(50) as Declare (SintEqId int  Select @intEqId  = EquipmentId from dbo.Equipment where Make = @chvMake and Model = @chvModel) Return @intEqId 

The same functionality could be achieved even without a local variable, since a Return statement can accept an integer expression instead of an integer value:

 Create Procedure dbo.ap_Eq!dByMakeModel_List_4      @chvMake varchar(50),      @chvModel varchar(50) as    Return (select EqId            from dbo.Equipment            where Make = @chvMake            and Model = @chvModel) 

To execute the stored procedure and access the returned value, we require the following lines of code:

 Declare @intEqId int Execute @intEqId = ap_Eq!dByMakeModel_List_3 'Toshiba', 'Portege 7020CT' Select @intEqId 'Equipment Identifier') 

Notice the difference in assigning a value. The local variable must be inserted before the name of the stored procedure. The result of the batch is the returned value:

 Equipment Identifier -------------------- 1 (1 row(s) affected) 

This solution, however, is not a perfect way to transfer information from a stored procedure to a caller. In the first place, it is limited by data type. Only integers can be returned this way (including int, smallint, and tinyint). This method was often used in old versions of SQL Server to return status information to the caller:

 Create Procedure dbo.ap_Eq!dByMakeModel_List_5      @chvMake varchar(50),      @chvModel varchar(50),      @intEqId int output as      select @intEqId = EqId      from dbo.Equipment      where Make = @chvMake      and Model = @chvModel Return @@error 

In this example, the stored procedure will potentially return an error code. @@error is a scalar function that contains an error number in the case of failure or a zero in the case of success. To execute the stored procedure, use the following code:

 Declare   @intEqId int,      @intErrorCode int Execute @intErrorCode = dbo.ap_EqIdByMakeModel_List_5                                    'Toshiba',                                    'Portege 7020CT',                                    @intEqId output Select @intEqId result, @intErrorCode ErrorCode 

The result will look like this:

 result      ErrorCode ----------- ---------- 1           0 (1 row(s) affected) 

An ErrorCode of 0 indicates the stored procedure was executed successfully without errors.

Default Values

If the stored procedure statement has parameters, you must supply values for the parameters in your Exec statement. If a user fails to supply them, the server reports an error. It is possible, however, to assign default values to the parameters so that the user is not required to supply them. Default values are defined at the end of a parameter definition, behind the data types. All that is needed is an assignment (=) and a value:

 create Procedure dbo.ap_EqIdByMakeModel_List_6      @chvMake  varchar(50) = '%',           @chvModel varchar(50) = '%' as      Select *      from dbo.Equipment      where Make Like @chvMake      and Model Like @chvModel 

The procedure is designed as a small search engine that accepts T-SQL wild cards. You can execute this stored procedure with normal values:

  Execute dbo.ap_EqIdByMakeModel_List_6 'T%' , 'Portege%' 

The result set will consist of records that match the criteria:

 EqId        Make                 Model                EqTypeId ----------- -------------------- -------------------- -------- 1           Toshiba              Portege 7020CT       1 34          Toshiba              Portege 7021CT       1 (2 row(s) affected) 

If one parameter is omitted, as follows, the procedure will behave, since the value that was defined as a default has been supplied:

 Execute dbo.ap_EqIdByMakeModel_List_6 'T%' 

The server will return the following result set:

 EqId        Make                  Model               EqTypeId ----------- --------------------- ------------------- -------- 1           Toshiba               Portege 7020CT      1 34          Toshiba               Portege 7021CT      1 (2 row(s) affected) 

Even both parameters may be skipped:

 Execute dbo.ap_EqIdByMakeModel_List_6 

The server will return all records that match the default criteria:

 EqId         Make                 Model              EqTypeId ------------ -------------------- ------------------ -------- 1           Toshiba               Portege 7020CT     1 2           Sony                  Trinitron 17XE     3 ... 

Passing Parameters by Name

You do not have to follow parameter order if you pass parameters by name. You must type the name of the parameter and then assign a value to it. The parameter name must match its definition, including the @ sign.

This method is sometimes called passing parameters by name. The original method can be referred to as passing parameters by position. In the following example, the server will use T% for the second parameter and a default value, %, for the first one:

 Execute dbo.ap_EqIdByMakeModel_List_6 ©Model = 'T%' 

The result of the search will be the following:

 EqId        Make               Model                        EqTypeId ----------- ------------------ ---------------------------- ------- 449         Compaq             TP DT ACMS ALL LPS Pers Use 3 855         Compaq             TP DT BCMS ALL LPS Pers Use 3 ... 

The opportunity to skip parameters is just one reason for passing parameters by name. Even more important is the opportunity to create a method that makes code more readable and maintainable. And, if a developer makes a mistake and assigns a value to a nonexistent parameter, the error will be picked up by SQL Server.

Tip 

Although passing parameters by position can he a little faster, passing parameters hy name is preferable.

Syntax

The following is the complete syntax for the creation of a stored procedure:

 CREATE PROG[EDURE] schema. procedure_name [;number]     [         {@parameter schema.data_type} [VARYING]  [= default]  [OUTPUT]     ]     [,...n] [WITH {    RECOMPILE         |  ENCRYPTION         |  EXECUTE AS { CALLER | SELF | OWNER | 'user_name' }       } ] [FOR REPLICATION] AS      sql_statement [...n] 

When you create a stored procedure using With Encryption, the code of the stored procedure is encrypted and then saved in the database. SQL Server will be able to use the encrypted version of the source code to recompile the stored procedure when needed, but none of the users (not even the system administrator) will be able to obtain it.

Note 

That was the theory. In reality, you should not count on SQL Server encryption to protect your code. It is possible to find on the Internet the means to defeat SQL Server encryption. Copyright and good support are much better protection for the company's interests when you deploy stored procedures on the server of your client.

Keep in mind that you will not be able to change a stored procedure if you create the procedure using With Encryption. You must preserve its code somewhere else (ideally, in a source-code management system like Visual SourceSafe, described in Chapter 16). For more details about storage and encryption of stored procedures, see "Storing Stored Procedures" in Appendix B.

As a developer, you might decide to recompile a stored procedure each time it is used. To force compilation, you should create the stored procedure using With Recompile. Recompiling for each use may improve or degrade the performance of the stored procedure: Although the compilation process is extra overhead when you are executing the stored procedure, SQL Server will sometimes recompile the stored procedure differently (and more economically) based on the data it is targeting. You will find more details about compilation and reasons for recompiling a stored procedure later in this chapter.

[;number] is an optional integer value that can be added to the name of a stored procedure. In this way, a user can create a group of stored procedures that can be deleted with a single Drop Procedure statement. Procedures will have names such as these:

• ap_Equipment_List;1

• ap_Equipment_List;2

• ap_Equipment_List;3

Numbering of stored procedures is sometimes used during development and testing, so that all nonproduction versions of a procedure can be dropped simultaneously and quickly.

Stored procedures that include the For Replication option are usually created by SQL Server to serve as a filter during the replication of databases.

An output parameter for a stored procedure can also be of the cursor data type. In such a case, the structure of the result set contained by the cursor might vary. The [Varying] option will notify SQL Server to handle such cases. But it is too early to talk about cursors. We will return to cursors in the next chapter.

The Execute As clause is introduced in SQL Server 2005. It allows a developer to specify an execution context of a stored procedure (and other programmatic database objects). A developer can specify which user SQL Server should use to validate permissions on database objects referenced by the stored procedure. You can find more information about this in Chapter 19.

All of these options involve rarely used features. Some of them will be covered in more detail later in this book, but some are simply too esoteric.