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.
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.
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.
/* 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.
/* 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