Stored Procedure Coding Guidelines and Limitations

Stored procedures should be treated just like reusable application code. Follow these suggested guidelines to ensure your stored procedures are solid and robust:

• Check all parameters for validity and return an error message if a problem exists.

• Be sure that the parameter datatypes match the column datatypes they are compared against to avoid datatype mismatches and poor query optimization.

• Check the @@error system function after each SQL statement, especially insert , update , and delete , to verify that the statements executed successfully. Return a status code other than 0 if a failure occurs.

• Be sure to comment your code so that when you or others have to maintain it, the code is self-documenting .

• Consider using a source code management system, such as Microsoft Visual Studio SourceSafe or PVCS, to maintain versions of your stored procedure source code.

Avoid using " select * ... " in your stored procedure queries. In the event someone adds columns to, or removes columns from a table, the stored procedure will generate a different resultset, which could potentially break application code.

Whenever using INSERT statements in stored procedures, always provide the column list associated with the values being inserted. This will allow the procedure to continue to work if the table is ever rebuilt with a different column order or additional columns are added to the table. Listing 28.30 demonstrates what happens if the column list is not provided and a column is added to the referenced table.

Listing 28.30 Lack of Column List in Insert Statement Causes Procedure to Fail If Column Is Added to Table

 create proc insert_publishers @pub_id char(4),                               @pub_name varchar(40),                               @city varchar(20),                               @state char(2),                               @country varchar(30) as INSERT INTO pubs.dbo.publishers VALUES(@pub_id, @pub_name, @city, @state, @country) if @@error = 0     print 'New Publisher added' go exec insert_publishers '9911', 'Sams Publishing', 'Indianapolis', 'IN', 'USA' go New Publisher added alter table publishers add street varchar(80) null go exec insert_publishers '9912', 'Pearson Education', 'Indianapolis', 'IN', 'USA' go Server: Msg 213, Level 16, State 4, Procedure insert_publishers, Line 3 Insert Error: Column name or number of supplied values does not match table  definition. 

A stored procedure cannot directly create views, triggers, defaults, rules, or other stored procedures. You can, however, execute a dynamic SQL string that creates the object:

 CREATE PROC create_other_proc AS    EXEC ('CREATE PROC get_au_lname AS         SELECT au_lname from authors         RETURN') 

You can create tables in stored procedures. Generally, only temporary tables are created in stored procedures. Temporary tables created in stored procedures are dropped automatically when the procedure terminates. Global temporary tables, however, exist until the connection that created them terminates.

You cannot drop a table and re-create another table with the same name within the procedure unless you use dynamic SQL to execute a string that creates the table.

A stored procedure cannot issue the USE statement to change the database context in which it is running; the database context for execution is limited to a single database. If you need to reference an object in another database, qualify the object name with the database name in your procedure code.

Calling Stored Procedures from Transactions

Stored procedures can be called from within a transaction as well as initiate a transaction. SQL Server notes the transaction nesting level, which is available from the @@trancount function, before calling a stored procedure. If the transaction nesting level when the procedure returns is different from the level when it is executed, SQL Server displays the following message: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing . This message indicates that transaction nesting is out of balance. Because a stored procedure does not abort the batch on a rollback transaction , a rollback transaction inside the procedure could result in a loss of data integrity if subsequent statements are executed and committed.

A rollback transaction statement rolls back all statements to the outermost transaction, including any work performed inside nested stored procedures that have not been fully committed. A commit tran within the stored procedure decreases the @@trancount by only one. Because the transaction is not fully committed until @@trancount returns to 0, the work can be completely rolled back at any time prior to that.

You need to develop a consistent error-handling strategy for failed transactions or other errors that occur within transactions within your stored procedures and implement this strategy consistently across all procedures and applications. Within stored procedures that might be nested, you need to check whether the procedure is already being called from within a transaction before issuing another begin tran statement. If a transaction is already active, issue a save tran statement so that the procedure can roll back only the work that it has performed and allow the calling proc that initiated the transaction determine whether to continue or abort the overall transaction.

To maintain transaction integrity when calling procedures that involve transactions, follow these guidelines:

• Make no net change to @@trancount within your stored procedures.

• Issue a begin tran only if no transaction is already active.

• Set a savepoint if a transaction is already active so that a partial rollback can be performed within the stored procedure.

• Implement appropriate error handling and return an error status code if something goes wrong and a rollback occurs.

• Issue a commit tran only if the stored procedure issued the begin tran statement.

Listing 28.31 provides a template for a stored procedure that can provide transactional integrity whether it is run as part of an ongoing transaction or run independently.

Listing 28.31 Template Code for a Stored Procedure That Can Run as Part of a Transaction or Run As Its Own Transaction

 /* proc to demonstrate no net change to @@trancount ** but rolls back changes within the proc ** VERY IMPORTANT: return an error code ** to tell the calling procedure rollback occurred */ create proc p1 as declare @trncnt int select @trncnt = @@trancount  -- save @@trancount value if @trncnt = 0   -- transaction has not begun   begin tran p1  -- begin tran increments nest level to 1 else             -- already in a transaction   save tran p1   -- save tran doesn't increment nest level /* do some processing */ if (@@error != 0) -- check for error condition begin     rollback tran p1  -- rollback to savepoint, or begin tran     return 25           -- return error code indicating rollback end /* more processing if required */ if @trncnt = 0     -- this proc issued begin tran   commit tran p1   -- commit tran, decrement @@trancount to 0                    -- commit not required with save tran return 0 /* successful return */ 

Listing 28.32 provides a template for the calling batch that might execute the stored procedure shown in Listing 28.31. The main problem you need to solve is handling return codes properly and responding with the correct transaction handling.

Listing 28.32 Template Code for a Calling Batch or Stored Procedure That Might Execute a Stored Procedure Built with the Template in Listing 28.31

 /* Retrieve status code to determine if proc was successful */ declare @status_val int, @trncnt int select @trncnt = @@trancount  -- save @@trancount value if @trncnt = 0   -- transaction has not begun   begin tran t1  -- begin tran increments nest level to 1 else             -- otherwise, already in a transaction   save tran t1   -- save tran doesn't increment nest level /* do some processing if required */ if (@@error != 0) -- or other error condition begin     rollback tran t1  -- rollback to savepoint,or begin tran     return            -- and exit batch/procedure end execute @status_val = p1 --exec procedure, begin nesting if @status_val = 25 -- if proc performed rollback begin        -- determine whether to rollback or continue     rollback tran t1     return end /* more processing if required */ if @trncnt = 0    -- this proc/batch issued begin tran   commit tran t1  -- commit tran, decrement @@trancount to 0 return            -- commit not required with save tran