Points to Avoid While Using Stored Procedures

Avoid the following while using stored procedures:

• Stored procedure grouping/versioning

• Nesting

• Generic stored procedures

• Many parameters

Each of these, although technically valid, should be avoided for logistical or maintenance reasons. We will discuss these reasons in the following sections.

Stored Procedure Grouping

Multiple stored procedures can be grouped together with a single name by specifying a group number at the time of creation, for example:

     GO     CREATE PROCEDURE GroupProcedure;1     AS       SELECT COUNT(*) FROM Employees       GO       CREATE PROCEDURE GroupProcedure;2       AS         SELECT COUNT(*) FROM Orders         GO         CREATE PROCEDURE GroupProcedure;3         AS           SELECT COUNT(*) FROM Customers           GO 

We simply specify the procedure name followed by its number in the group, or just the procedure name, to execute it.

For example, the command:

     EXEC GroupProcedure 

is equivalent to:

     EXEC GroupProcedure;1 

These commands will return a COUNT(*) from the Employees table

The command:

     EXEC GroupProcedure;2 

returns a COUNT(*) from Orders, while the command:

     EXEC GroupProcedure;3 -- Returns a COUNT(*) from Customers 

returns COUNT(*) from Customers.

We simply execute the DROP PROCEDURE statement followed by the procedure group name, to remove the entire group of stored procedures:

     DROP PROCEDURE GroupProcedure 

Important

It is not possible to drop individual members of the procedure group.

For example, the following command will result in error:

     DROP PROCEDURE GroupProcedure;3 

The error returned is:

     Server: Msg 170, Level 15, State 1, Line 1     Line 1: Incorrect syntax near ';'. 

A practical use for this functionality is yet unknown, as it adds unneeded complexity. In addition, if we have multiple procedures with multiple functions, it can be used to create individual procedures with specific names to identify the functions.

Even if the procedures are related, we can use a naming scheme that shows the procedures are related, but it still allows us to assign a name based on function. On the other hand, grouped procedures appear as a single procedure within tools, such as Query Analyzer's Object Browser. The developer must remember the arbitrary number corresponding to a particular function.

Important

Advanced features of SQL Server, such as deferred name resolution and auto-recompilation, reduce the need to drop and recreate multiple procedures. Hence, it should be considered for backward compatibility only, unless you think of a practical use for this feature.

Nesting Stored Procedures

Stored procedures can be nested (one procedure calling another procedure, which in turn calls another procedure, and so on). However, there is a limit of 32 levels on the nesting depth. If you are writing stored procedures that drill into other procedures use the @@Nestlevel global variable to examine the levels that the current execution is in, allowing you to exit gracefully when you reach the limit of 32 levels.

For more information on nesting stored procedures, refer to Chapter 1.

Generic Stored Procedures

We have discussed earlier that it is a good approach to create specific procedures to support application functions rather than generic procedures to minimize code to optimize the performance of stored procedures. For example, a stored procedure used to create an invoice is a specific function that can be optimized to provide acceptable performance in the context of the production environment.

Conceptually ‘creation’ procedures (procedures that update rows) can be generalized into a single procedure that accepts a bunch of parameters. One of these parameters will be the name of the table that the rows are to be updated. While this approach certainly reduces the amount of code to maintain, optimization may be difficult, as the SQL query and the indexes required to satisfy the query optimally may change between executions, depending on the combination of parameters used. In addition, logic outside the generic operation will need to be removed from the stored procedures and pushed back into the application tier, which may require additional network round trips for processing.

Important

Creating generic stored procedures is a common approach used by developers, who are experienced with traditional application development environments. This is a valid approach depending on your application architecture, however you should be aware that performance optimization might be more difficult if you keep your stored procedures generic.

Stored Procedures with Many Parameters

Stored procedures having many parameters go hand-in-hand with the generic procedures that we discussed in the previous section. Having many parameters is an indication that we have actually combined the logic for several stored procedures into one generic procedure. For example:

     CREATE PROCEDURE SelectProc       @Option1 INT=NULL,       @CustomerId INT=NULL,       @Option2 INT=NULL,       @OrderId INT=NULL,       @Option3 INT=NULL,       @EmployeeId INT=NULL     AS       SET NOCOUNT ON       IF @Option1 IS NOT NULL       BEGIN         SELECT *         FROM dbo.Customers         WHERE CustomerID=@CustomerID       END       IF @Option2 IS NOT NULL       BEGIN         SELECT *         FROM dbo.Orders         WHERE OrderID=@OrderID     END     IF @Option3 IS NOT NULL     BEGIN       SELECT *       FROM dbo.Employees       WHERE EmployeeID=@EmployeeID     END 

The client application must be aware of the type of information it requires to correctly set the parameters for calling this stored procedure correctly. If this is the case, it is much more desirable and maintainable to have separate stored procedures, each with specific parameters for the specific function of the procedure. For example:

     CREATE PROCEDURE GetCustomers @CustomerId INT=NULL     AS       SET NOCOUNT ON       SELECT *       FROM dbo.Customers       WHERE CustomerID=@CustomerID     GO     CREATE PROCEDURE GetOrders @OrderId INT=NULL     AS       SET NOCOUNT ON       SELECT *       FROM dbo.Orders       WHERE OrderID=@OrderID     GO     CREATE PROCEDURE GetEmployees @EmployeeId INT=NULL     AS       SET NOCOUNT ON       SELECT *       FROM dbo.Employees       WHERE EmployeeID=@EmployeeID       GO 

The second approach may result in a slightly increased number of network round trips to retrieve the same information; however, we have the additional benefit of being able to alter, optimize, and maintain each stored procedure individually.