http://msdn2.microsoft.com/en-us/library/aa175920.aspx
Error Handling in T-SQL: From Casual to Religious
Dejan Sunderic
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.
Why bother?
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
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.
Conclusion
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. dejans@hotmail.com.
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.