This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.
Error Handling in T-SQL: From Casual to Religious
Most of us would agree that experienced programmers tend to be more adept at (and perhaps even more "religious" about) error handling than rookies. VB and C/C++ programmers are so spoiled by the error-handling tools in their IDEs that they sometimes forget good old-fashioned "roll your own" error handling. In this article, Dejan Sunderic provides some guidance for both DBAs and database application developers.
T-SQL is rather laconic (critics would say feature-poor)especially when it comes to error handling, and DBAs, who tend to write a lot of rather straightforward scripts, are often guilty of neglecting the kind of robust error handling that applications need. And to complicate matters, logic thats fine in standard languages like VB or C/C++ might not even work in T-SQL. For example, it might make sense to let a T-SQL script continue to run even after an error occursassuming the error is "noncritical."
Another typical error that T-SQL tyros often make is to check the value of global variables and then to try to return or process it:
Update 	 If @@Error <> 0 Begin Select 'Unexpected error occurred: ', @@Error Return @@Error End
Although code like this is common in VB, in this case, the stored procedure will return 0 as the error number. Thats because SQL Server sets the value of @@Error variable after each statement. Assuming successful completion of the If statement, the final value of @@Error will be 0.
For many, the question is, "Why bother?" Lets look at a simple example:
Begin transaction Update Set Where Update Set Where Commit transaction
Most DBAs would cringe at code like this because weve learned to make transactions as granular as possible. Inexperienced T-SQL programmers, however, might not be familiar with transaction processing and thus not realize that, if errors occurred while processing the second UPDATE, SQL Server would still unconditionally commit the first (successful) UPDATE. (Imagine the classic ATM example where funds were debited from one account, but never credited to the second account.)
"Casual" error handling
Intermediate programmers who understand the nature of transaction processing arent likely to make that kind of error. They might write code like this:
Begin transaction Update If @@error <> 0 Begin Select 'Unexpected error occurred!' Rollback transaction Return 1 End Update If @@error <> 0 Begin Select 'Unexpected error occurred!' Rollback transaction Return 1 End Commit Transaction
However, this solution contains a lot of repetitive code. The logical next step is to group some of the code into a generic error-handling procedure such as this:
Begin transaction Update . If @@error <> 0 goto ERR_HANDLER Delete If @@error <> 0 goto ERR_HANDLER Commit Transaction Return 0 ERR_HANDLER: Select 'Unexpected error occurred!' Rollback transaction Return 1
Although this is certainly an improvement, it still doesnt deal with all issues that need to be handled.
A more coherent (religious) solution
Lets try to develop a generic, yet comprehensive solution for error handling in T-SQL. The basic idea is that all SQL statements inside a stored procedure should be covered with error-handling code. Any time an unexpected error occurs, a stored procedure should stop further processing. Furthermore, not only will this impact the stored procedure itself, but it will also impact any stored procedure(s) that have called it.
The basic element of the solution is that all SQL statements should be followed with a statement that reads the contents of @@Error variable to determine if the statement completed successfully.
Declare @ErrorCode int Select @ErrorCode = @@Error If @ErrorCode = 0 Begin --Some statement Update Select @ErrorCode = @@Error End If @ErrorCode = 0 Begin --Another statement Insert Select @ErrorCode = @@Error End If @ErrorCode = 0 Begin --Another statement Update Select @ErrorCode = @@Error End Return @ErrorCode
At the end, the stored procedure should return the value of @ErrorCode variable to the calling stored procedure or script. If errors have occurred, this might be used to notify the calling procedure that there was a problem.
Nested stored procedures
Okay, but what about nested stored procedures? Well, calls to stored procedures should treat return values as error codes, as follows:
If @ErrorCode = 0 Begin execute @ErrorCode = MyStoredProcedure parm1, param2 End
This system works like a cascade and will stop all further processing in whole sets of nested stored procedures.
Interfacing other environments
This structure is very useful even in cases when a stored procedure was called from some other programming environment, such as VB or Visual C++. The return value of a stored procedure can be retrieved and an error can be handled on that level as well.
conn.Open "provider=sqloledb;data source=sqlserver;" _ + "user id=sa;password=;initial catalog=pubs" cmd.CommandText = "exec test_proc" cmd.CommandType = adCmdStoredProc cmd.Parameters.Append cmd.CreateParameter("RetVal", _ adInteger, adParamReturnValue) Set rs = cmd.Execute() lngReturnValue = rs(0) If lngReturnValue <> 0 Then Begin MsgBox "Procedure has failed!" Exit Sub End Else MsgBox "Procedure was successful!"
Other global variables
There are cases when you wish to read the value of some other global variables immediately after the statement, and you should handle them with the same select statement that reads @@Error. For example, you often require something like this when youre using identity columns.
Insert Select @id = @@identity, @ErrorCode = @@Error
Transaction processing can be perfectly integrated with this solution. At the beginning of a stored procedure (or transaction), the developer should add the following:
Declare @TransactionCountOnEntry int If @ErrorCode = 0 Begin Select @TransactionCountOnEntry = @@TranCount BEGIN TRANSACTION End
At the end of the procedure/transaction, the developer should complete the transaction as follows:
If @@TranCount > @TransactionCountOnEntry Begin If @ErrorCode = 0 COMMIT TRANSACTION Else ROLLBACK TRANSACTION End
The solution will also perform well in the case of nested stored procedures. All procedures will be rolled back using the same cascading mechanism. Here, the local variable @TransactionCountOnEntry is used to track the number of opened transactions upon the entry of a stored procedure. If the number was unaffected inside the stored procedure, theres no reason to either commit or rollback inside the procedure.
Critics might have objections to the proposed solution. For example, it adds a lot of code in the script, and it would be improved if the developer can "hide" it to place statements doing "real" processing in the forefront. But the solutions real value is that it will permit code in stored procedures to work in a uniform manner and developers to know what to expect when the unexpected occurs.
Dejan Sunderic is currently working as the principal consultant for the Toronto-based Trigon Blue, Inc. His specialty is development and project management of B2B eCommerce, OLTP, and decision-support systems. firstname.lastname@example.org.
To find out more about Microsoft SQL Server Professional and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57
Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.
This article is reproduced from the June 2000 issue of Microsoft SQL Server Professional. Copyright 2000, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Microsoft SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.