DATABASE

SQL Server 2005 : Exception Handling

10/12/2010 9:42:26 AM
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.

Other  
 
Video
Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
REVIEW
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone