Stored procedures should be treated just like reusable application code. Follow these suggested guidelines to ensure your stored procedures are solid and robust:
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:
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