Exception Handling in T-SQL Using @@ERROR: Why Bother?
This is Part 6 of a series of blog posts by Data Education founder Adam Machanic on errors and exceptions in Microsoft SQL Server. In Part 1, Adam gave a basic explanation of the difference between errors and exceptions. In Part 2, he examined types of exceptions. In Part 3, Adam broke down the parts of the dreaded error message. In Part 4, he took a steely-eyed look at the
RAISERROR function. In the last post, Part 5, Adam discussed monitoring exception events with traces. Here, he offers his arguments for why to handle exceptions in T-SQL using
Understanding when, why, and how SQL Server throws exceptions is great, but the real goal is to actually do something when an exception occurs. Exception handling refers to the ability to catch an exception when it occurs, rather than simply letting it bubble up to the next level of scope. This is a capability that was not possible in T-SQL until SQL Server 2005, and its addition to the language added some interesting development possibilities.
Exception handling in T-SQL should be thought of as no different from exception handling in any other language. A generally accepted programming practice is to handle exceptions at the lowest possible scope, in order to keep them from interacting with higher levels of the application. If an exception can be caught at a lower level and dealt with there, higher-level modules will not require special code to handle the exception and therefore can concentrate on whatever their purpose is. This means that every routine in the application becomes simpler, more maintainable, and therefore quite possibly more robust.
Put another way, exceptions should be encapsulated as much as possible; knowledge of the internal exceptions of other modules is yet another form of coupling.
Keep in mind that encapsulation of exceptions is really something that must be handled on a case-by-case basis. But the basic rule is: If you can “fix” the exception one way or another without letting the caller ever know it even occurred, that is probably a good place to encapsulate.
Exception “Handling” Using @@ERROR
Versions of SQL Server prior to SQL Server 2005 did not have true exception-handling capabilities. Any exception that occurred would be passed back to the caller, regardless of any action taken by the code of the stored procedure or query in which it was thrown. The general method used to “handle” errors in those versions of SQL Server is still useful in some cases—and a lot of legacy code is still around—so a quick review is definitely warranted.
@@ERROR function is quite simple: It returns 0 if the last statement in the batch did not throw an error of severity 11 or greater. If the last statement did throw an error, it returns the error number. For example, consider the following T-SQL:
SELECT 1/0 AS DivideByZero SELECT @@ERROR AS ErrorNumber
This returns the following output:
DivideByZero ----------- Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. ErrorNumber ----------- 8134 (1 row(s) affected)
By checking to see whether the value of
@@ERROR is nonzero, it is possible to do some very primitive error handling. Unfortunately, this is also quite error-prone, due to the nature of
@@ERROR and the fact that it only operates on the last statement executed in the batch. Many developers new to T-SQL are quite surprised by the output of the following batch:
SELECT 1/0 AS DivideByZero IF @@ERROR <> 0 SELECT @@ERROR AS ErrorNumber
The output result is as follows:
DivideByZero ------------ Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. ErrorNumber ----------- 0 (1 row(s) affected)
The solution to this problem is to set a variable to the value of
@@ERROR after every statement in a batch that requires error handling. Of course, if even a single statement is missed, holes may be left in the strategy, and some errors may escape notice.
Even with these problems,
@@ERROR still has a place in SQL Server 2005 and beyond. It is a simple, lightweight alternative to the full-blown exception-handling capabilities that have been added to the language, and it has the additional benefit of not catching the exception. In some cases, full encapsulation is not the best option, and using
@@ERROR will allow the developer to take some action—for instance, logging of the exception—while still passing it back to the caller.