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 has not been
possible in T-SQL until SQL Server 2005, and its addition to the
language adds some interesting development possibilities.
Why Handle Exceptions in T-SQL?
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, not much different than some
of the types discussed in the first chapter of this book.
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 will be around for quite
a while—so a quick review is definitely warranted.
The @@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
@Results:-----------
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
@Results:------------
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. 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.
SQL Server's TRY/CATCH Syntax
The standard error handling construct in many programming languages—now including T-SQL—is known as try/catch. The idea behind this construct is to set up two sections (a.k.a. blocks) of code. The first section, the try block,
contains exception-prone code to be "tried." The second section
contains code that should be executed in the event that the code in the
try block fails, and an exception occurs. This is called the catch block.
As soon as any exception occurs within the try block, code execution
immediately jumps into the catch block. This is also known as catching an exception.
In T-SQL, try/catch is implemented using the following basic form:
BEGIN TRY
--Code to try here
END TRY
BEGIN CATCH
--Catch the exception here
END CATCH
Any type of exception—except for connection or server-level exceptions—that occurs between BEGIN TRY and END TRY will cause the code between BEGIN CATCH and END CATCH to be immediately executed, bypassing any other code left in the try block.
As a first example, consider the following T-SQL:
BEGIN TRY
SELECT 1/0 AS DivideByZero
END TRY
BEGIN CATCH
SELECT 'Exception Caught!' AS CatchMessage
END CATCH
Running this batch produces the following output:
DivideByZero
@Results:------------
(0 row(s) affected)
CatchMessage
-----------------
Exception Caught!
(1 row(s) affected).
The interesting things to
note here are that, first and foremost, there is no reported exception.
We can see that an exception occurred because code execution jumped to
the CATCH block, but the
exception was successfully handled, and the client is not aware that an
exception occurred. Second, notice that an empty result set is returned
for the SELECT statement that caused
the exception. Had the exception not been handled, no result set would
have been returned. By sending back an empty result set, the implied
contract of the SELECT statement is honored (more or less, depending on what the client was actually expecting).
Although already mentioned, it needs to be stressed that when using TRY/CATCH, all exceptions within the TRY block will immediately abort execution of the remainder of the TRY block. Therefore, the following T-SQL has the exact same output as the last example:
BEGIN TRY
SELECT 1/0 AS DivideByZero
SELECT 1 AS NoError
END TRY
BEGIN CATCH
SELECT 'Exception Caught!' AS CatchMessage
END CATCH
Finally, it is worth noting that parsing and compilation exceptions will not be caught using TRY/CATCH,
nor will they ever have a chance to be caught—an exception will be
thrown by SQL Server before any of the code is ever actually executed.
Getting Extended Error Information in the Catch Block
In
addition to the ability to catch an exception, SQL Server 2005 offers a
series of new functions that are available within the CATCH
block. These functions, a list of which follows, enable the developer
to write code that retrieves information about the exception that
occurred in the TRY block.
ERROR_MESSAGE
ERROR_NUMBER
ERROR_SEVERITY
ERROR_STATE
ERROR_LINE
ERROR_PROCEDURE
These
functions take no input arguments and are fairly self-explanatory based
on their names. However, it is important to point out that unlike @@ERROR, the values returned by these functions are not reset after every statement. They are persistent for the entire CATCH block. Therefore, logic such as that used in the following T-SQL works:
BEGIN TRY
SELECT CONVERT(int, 'ABC') AS ConvertException
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 123
SELECT 'Error 123'
ELSE
SELECT ERROR_NUMBER() AS ErrorNumber
END CATCH
As expected, in this case the error number is correctly reported:
ConvertException
@Results:----------------
(0 row(s) affected)
ErrorNumber
-----------
245
(1 row(s) affected).
These functions, especially ERROR_NUMBER,
allow for coding of specific paths for certain exceptions. For example,
if a developer knows that a certain piece of code is likely to cause an
exception that can be programmatically fixed, that exception number can
be checked for in the CATCH block.
Rethrowing Exceptions
A common feature in most languages that have try/catch capabilities is the ability to rethrow
exceptions from the catch block. This means that the exception that
originally occurred in the try block will be raised again, as if it was
not handled at all. This is useful when you need to do some handling of
the exception but also let the caller know that something went wrong in
the routine.
T-SQL does not include any kind of built-in rethrow functionality. However, it is fairly easy behavior to mock up based on the CATCH block error functions, in conjunction with RAISERROR. The following example shows a basic implementation of rethrow in T-SQL:
BEGIN TRY
SELECT CONVERT(int, 'ABC') AS ConvertException
END TRY
BEGIN CATCH
DECLARE
@ERROR_SEVERITY INT,
@ERROR_STATE INT,
@ERROR_NUMBER INT,
@ERROR_LINE INT,
@ERROR_MESSAGE VARCHAR(245)
SELECT
@ERROR_SEVERITY = ERROR_SEVERITY(),
@ERROR_STATE = ERROR_STATE(),
@ERROR_NUMBER = ERROR_NUMBER(),
@ERROR_LINE = ERROR_LINE(),
@ERROR_MESSAGE = ERROR_MESSAGE()
RAISERROR('Msg %d, Line %d: %s',
@ERROR_SEVERITY,
@ERROR_STATE,
@ERROR_NUMBER,
@ERROR_LINE,
@ERROR_MESSAGE)
END CATCH
Due to the fact that RAISERROR
cannot be used to throw exceptions below 13000, in this case
"rethrowing" the exception requires raising a user-defined exception
and sending back the data in a specially formed error message. As
functions are not allowed within calls to RAISERROR, it is necessary to define variables and assign the values of the error functions before calling RAISERROR to rethrow the exception. Following is the output of this T-SQL:
ConvertException
@Results:----------------
(0 row(s) affected)
Msg 50000, Level 16, State 1, Line 19
Msg 245, Line 2: Conversion failed when converting the varchar value 'ABC'
to data type int.
Keep
in mind that based on your interface requirements, you may not always
want to rethrow the same exception that was caught to begin with. It
might make more sense, in many cases, to catch the initial exception,
and then throw a new exception that makes more sense (or is more
helpful) to the caller. For example, if you're working with a linked
server and the server is not responding for some reason, your code will
throw a timeout exception. It might make more sense to pass back a
generic "data not available" exception than to expose the actual cause
of the problem to the caller. This is something that should be decided
on a case-by-case basis, as you work out optimal designs for your
stored procedure interfaces.
When Should TRY/CATCH Be Used?
As
mentioned previously, the general use case for handling exceptions in
T-SQL routines (such as within stored procedures) is to encapsulate as
much as possible at as low a level as possible, in order to simplify
the overall code of the application. A primary example of this is
logging of database exceptions. Instead of sending an exception that
cannot be properly handled back to the application tier where it will
be logged back to the database, it probably makes more sense to log it
while already in the scope of a database routine.
Another
use case is temporary fixes for problems stemming from application
code. For instance, the application—due to a bug—might occasionally
pass invalid keys to a stored procedure that is supposed to insert them
into a table. It might be simple to temporarily
"fix" the problem by simply catching the exception in the database
rather than throwing it back to the application where the user will
receive an error message. Putting quick fixes of this type into place
is often much cheaper than rebuilding and redeploying the entire
application.
It is also important to consider when not
to encapsulate exceptions. Make sure not to overhandle security
problems, severe data errors, and other exceptions that the
application—and ultimately, the user—should probably be informed of.
There is definitely such a thing as too much exception handling, and
falling into that trap can mean that problems will be hidden until they
cause enough of a commotion to make themselves impossible to ignore.
Long-term
issues hidden behind exception handlers usually pop into the open in
the form of irreparable data corruption. These situations are usually
highlighted by a lack of viable backups because the situation has been
going on for so long, and inevitably end in lost business and
developers getting their resumes updated for a job search. Luckily,
avoiding this issue is fairly easy. Just use a little bit of common
sense, and don't go off the deep end in a quest to stifle any and all
exceptions.
Using TRY/CATCH to Build Retry Logic
An interesting example of where TRY/CATCH
can be used to fully encapsulate an exception is when dealing with
deadlocks. Although it's better to try to find and solve the source of
a deadlock than to code around it, this is often a difficult and
time-consuming task. Therefore, it's common to deal with deadlocks—at
least temporarily—by having the application reissue the request that
caused the deadlock. Eventually the deadlock condition will resolve
itself (i.e., when the other transaction finishes), and the DML
operation will go through as expected.
By using T-SQL's TRY/CATCH
syntax, the application no longer needs to reissue a request or even
know that a problem occurred. A retry loop can be set up, within which
the deadlock-prone code can be tried in a TRY block and the deadlock caught in a CATCH block in order to try again.
A basic implementation of a retry loop follows:
DECLARE @Retries INT
SET @Retries = 3
WHILE @Retries > 0
BEGIN
BEGIN TRY
/*
Put deadlock-prone code here
*/
--If execution gets here, success
BREAK
END TRY
BEGIN CATCH
IF ERROR_NUMBER = 1205
BEGIN
SET @Retries = @Retries - 1
IF @Retries = 0
RAISERROR('Could not complete transaction!', 16, 1)
END
ELSE
RAISERROR('Non-deadlock condition encountered', 16, 1)
END CATCH
END
In this example, the deadlock-prone code is retried as many times as the value of @Retries. Each time through the loop, the code is tried. If it succeeds without an exception being thrown, the code gets to the BREAK and the loop ends. Otherwise, execution jumps to the CATCH
block, where a check is made to ensure that the error number is 1205
(deadlock victim). If so, the counter is decremented so that the loop
can be tried again. If the exception is not a deadlock, another
exception is thrown so that the caller knows that something went wrong.
It's important to make sure that the wrong exception does not trigger a
retry.
A Final Note: Defensive Programming
Exception
handling is extremely useful, and its addition to T-SQL is absolutely
invaluable. However, I hope that all readers keep in mind that
exception handling is no substitute for proper checking of error
conditions before they occur. Whenever possible, code defensively. Proactively look for problems, and if they can be both detected and handled, code around them.
Remember that it's generally a better idea to handle exceptions rather than errors.
If you can predict a condition and write a code path to handle it
during development, that will usually provide a much more robust
solution than trying to trap the exception once it occurs and handle it
then.
