Best Practices For Stored Procedures

Stored sets of Transact-SQL statements contained within the SQL Server database are called stored procedures, which are compiled so that they rapidly execute SQL statements. You can use stored procedures in addition to programs for database access and manipulation because they can use variables and parameters, return errors and status, and use flow control to control the execution order of SQL statements. Because stored procedures are compiled and stored in the procedure cache when they're first executed, they can store invalid access paths to data based on the index statistics at the time the procedure was first run. To force a stored procedure to refresh its access path, use the system-stored procedure sp_recompile and pass the table that was updated as a parameter. For example, sp_recompile authors will force all the procedures that use the authors table to be recompiled the next time they're executed. Stored procedures are compiled the first time that they're run and are stored in a system table of the current database. When they are compiled, they are optimized to select the best path to access information in the tables. This optimization takes into account the actual data patterns in the table, indexes that are available, table loading, and more. These compiled stored procedures can greatly enhance the performance of your system. * Another benefit is that you can execute a stored procedure on either a local or remote SQL Server. This enables you to run processes on other machines and work with information across servers, not just local databases. * An application program written in a language, such as C or Visual Basic, can also execute stored procedures, providing an optimum "working together" solution between the client side software and SQL Server. You use the CREATE PROCEDURE statement to create a stored procedure. Permission to execute the procedure that you create is set by default to the owner of the database. An owner of the database can change the permissions to allow other users to execute the procedure. The syntax that you use to define a new procedure is as follows: CREATE PROCEDURE [owner,] procedure_name [;number] [@parameter_name datatype [=default] [OUTput] ... [@parameter_name datatype [=default] [OUTput] [FOR REPLICATION] | [WITH RECOMPILE] , ENCRYPTION AS sql_statements

Classification : INTERNAL

Note that because SQL server attempts to optimize stored procedures by caching the most recently used routines, it is still possible that an older execution plan, one previously loaded in cache, may be used in place of the new execution plan. To prevent this problem, you must either drop and recreate the procedure or stop and restart SQL Server to flush the procedure cache and ensure that the new procedure is the only one that will be used when the procedure is executed. You can also create the procedure using a WITH RECOMPILE option so that the procedure is automatically recompiled each time that its executed. You should do this if the tables accessed by the queries in a procedure are very dynamic. Tables that are very dynamic have rows added, deleted, and updated frequently, which results in frequent changes to the indexes that are defined for the tables. Sets of Transact-SQL statements are referred to as batches, which includes stored procedures. The rules or syntax for the use of Transact-SQL statements in batch apply to the following list of objects: * Procedures * Rules * Defaults * Triggers * Views Imp----Transact-SQL contains several statements that are used to change the order of execution of statements within a set of statements such as a stored procedure. The use of such flow-control statements permit you to organize statements in stored procedures to provide the capabilities of a conventional programming language, such a C or COBOL. You may find that some of the retrieval, update, deletion, addition, and manipulation of the rows of database tables can more easily be performed through the use of flow-control statements in objects, such as stored procedures.

A trigger is a special type of stored procedure used to maintain referential integrity in a SQL Server database. You create insert, delete, and update triggers to control the addition, deletion, or updates to corresponding rows of related tables for which the trigger is defined. Triggers are an excellent way to maintain referential integrity because you have complete control over the operations that

Classification : INTERNAL

they perform, and they're server-based. Examining Limitations of Triggers SQL Server has some limitations on the types of SQL statements that can be executed while performing the actions of a trigger. The majority of these limitations are because the SQL cannot be rolled back (or inside a transaction), which may need to occur if the UPDATE, INSERT or DELETE that caused the trigger to execute in the first place is also rolled back. The following is a list of Transact-SQL statements that are not permitted to be in the body text of a trigger. SQL Server will reject the compilation and storing of a trigger with these statements: * All database and object creation statements: CREATE DATABASE, TABLE, INDEX, PROCEDURE, DEFAULT, RULE, TRIGGER, and VIEW * All DROP statements * Database object modification statements: ALTER TABLE and ALTER DATABASE * TRUNCATE TABLE DELETE triggers will not be executed when a TRUNCATE operation is initiated on a table. Because the TRUNCATE operation is not logged, there is no chance for the trigger to be run. However, permission to perform a TRUNCATE is limited to the table owner and to sa—and it cannot be transferred. * Object permissions: GRANT and REVOKE * UPDATE STATISTICS * RECONfigURE * Database load operations: LOAD DATABASE and LOAD TRANSACTION All physical disk modification statements: DISK... * Temporary table creation: either implicit through CREATE TABLE or explicit through SELECT INTO Additionally, the following are limitations that should be clearly understood: * A trigger may not be created on a view, but only on the base table or tables that the view was created on. * Any SET operations that change the environment, while valid, are only in effect for the life of the trigger. All values return to their previous states once the trigger has finished execution. * Manipulating binary large object (BLOB) columns of datatype TEXT

Classification : INTERNAL

or IMAGE, whether logged or not by the database, will not cause a trigger to be executed. * SELECT operations that return result sets from a trigger are not advised because of the very special handling of result sets that would be required by the client application code (whether in a stored procedure or not). Take care to make sure that all SELECT operations read their values into locally defined variables available in the trigger. Using Triggers In this section you will see several types of triggers being created for use. These examples aren't very sophisticated but should give you ideas on how you might implement triggers in your own environment. Triggers are fired or executed whenever a particular event occurs. In the following sections you will see the different events that can cause a trigger to be executed and some idea of what you may want to do on those events. Using INSERT and UPDATE Triggers INSERT and UPDATE triggers are particularly useful because they can enforce referential integrity constraints and make sure that your data is valid before it enters the table. Typically INSERT and UPDATE triggers are used to verify that the data on the columns being monitored by the trigger meets the criteria required or to update timestamp columns. Triggers are used when the criteria for verification is more complex than what can be represented in a declarative referential integrity constraint. In Listing 14.1, the trigger is executed whenever a record is inserted into the SALES table or when it is modified. If the order date is not during the first 15 days of the month, the record is rejected. Listing 14.1 14_1.SQL—Sales Trigger Disallowing Specified Records. Create Trigger Tri_Ins_Sales On SALES For INSERT, UPDATE As /* declare local variables needed */ Declare @nDayOfMonth tinyint

Classification : INTERNAL

/* Find the information about the record inserted */ Select @nDayOfMonth = DatePart( Day, I.ORD_DATE ) From SALES S, INSERTED I Where S.STOR_ID = I.STOR_ID And S.ORD_NUM = I.ORD_NUM And S.TITLE_ID = I.TITLE_ID /* Now test rejection criteria and return an error if necessary */ If @nDayOfMonth > 15 Begin /* Note: always Rollback first, you can never be sure what kind of error processing a client may do that may force locks to be held for unnecessary amounts of time */ ROLLBACK TRAN RAISERROR ( 'Orders must be placed before the 15th of the month', 16, 10 ) End Go Notice the way the INSERTED table is referred to in the previous join. This logical table is created specially by SQL Server to allow you to reference information in the record being modified. Using an alias I (as shown) makes it easy to reference the table in the join criteria specified in the Where clause. Using Nested Triggers Triggers can be nested up to 16 layers deep. However, if it is not desirable to have nested trigger operations, SQL Server can be configured to disallow them. Use the nested triggers option of sp_configure to toggle this option. See the Chapter 16 section entitled Displaying and Setting Server Options for more information on sp_configure and other options. Triggers become nested when, during execution of one trigger, it modifies another table on which there is another trigger—which is therefore executed. You can check your nesting level at any time by inspecting the value in @@NestLevel. The value will be between 0 and 16.

Classification : INTERNAL

SQL Server cannot detect nesting that causes an infinite loop during the creation of a trigger until the situation occurs at execution time. An infinite loop could be caused by having a trigger, TRIGGER_A on TABLE_A, that executes on an update of TABLE_A, causing an update on TABLE_B. TABLE_B has a similar trigger, TRIGGER_B, that is executed on an update and causes an update of TABLE_A. Thus, if a user updates either table, then the two triggers would keep executing each other indefinitely. If SQL Server detects such an occurrence, it shuts down or cancels the trigger. If a trigger causes an additional modification of the table from which it was executed, it does not cause itself to executed recursively. SQL Server has no support for re-entrant or recursive stored procedures or triggers in the current version. For example, suppose we have two triggers: one on the Sales table and one on the STORES table. The two triggers are defined in Listing 14.4. Listing 14.4 14_4.SQL—Two Triggers Nested if Delete Occurs on Sales Table /* First trigger deletes stores if the sales are deleted */ Create Trigger Tri_Del_Sales On SALES For DELETE As /* Announce the trigger being executed */ Print "Delete trigger on the sales table is executing..." /* declare a temp var to store the store that is being deleted */ Declare @sStorID char(4), @sMsg varchar(40) /* now get the value of the store being deleted */ Select @sStorID = STOR_ID From DELETED /* DELETED is a fake table created by SQLServer to hold the values of records deleted */ Group By STOR_ID /* Now delete the store record */ Select @sMsg = "Deleting store " + @sStorID

Classification : INTERNAL

Print @sMsg Delete Where Go

STORES STOR_ID = @sStorID

/* Second trigger deletes discounts if a store is deleted */ Create Trigger Tri_Del_Stores On STORES For DELETE As /* Announce the trigger being executed */ Print "Delete trigger on the Stores table is executing..." /* declare a temp var to store the store that is being deleted */ Declare @sStorID char(4), @sMsg varchar(40) /* now get the value of the store being deleted */ Select @sStorID = sTOR_ID From DELETED /* DELETED is a fake table created by SQLServer to hold the values of records deleted */ Group By STOR_ID If @@rowcount = 0 Begin Print "No Rows affected on the stores table" Return End /* Now delete the store record */ Select @sMsg = "Deleting discounts for store " + @sStorID Print @sMsg Delete DISCOUNTS Where STOR_ID = @sStorID Go If a Delete is executed on the Sales table (as shown in Listing 14.5), the trigger is executed on the Sales table, which in turns causes a trigger to execute on the Stores table.

Classification : INTERNAL

Listing 14.5—Results of Executing Delete on Sales Table /*----------------------------Delete from sales where stor_id = '8042' -----------------------------*/ Delete trigger on the sales table is executing... Deleting store 8042 Delete trigger on the Stores table is executing... Deleting discounts for store 8042 Triggers and DRI don't typically work together very well. For example, in Listing 14.5, you must first drop the Foreign Key constraint on the Discounts table for it to actually complete the delete. It is recommended that wherever possible you implement either triggers or DRI for integrity constraints. Displaying Trigger Information If you need to view the behavior that is being enforced on a table due to a trigger, you must display the information that describes the triggers (if any) that a table owns. There are a number of ways of obtaining information about a trigger that is on any given table. In this section, the two most common, using SQL Enterprise Manager (SQL EM) and the system procedures sp_help and sp_depends, will be demonstrated. In this chapter, you learned about the values of triggers and how they can be applied to enforce referential integrity in your application. In addition, you learned that triggers can be nested and that they can be used to provide more complex business rule validation than CONSTRAINTs that can be defined during table creation.

Classification : INTERNAL