SQL Server and the TRY/CATCH Syntax

This is Part 7 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. Within Part 6, he offered his arguments for why to handle exceptions in T-SQL using @@ERROR. Here, Adam explains the importance of the TRY/CATCH syntax.

The standard error handling construct in many programming languages—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
------------

(0 row(s) affected)

CatchMessage
-----------------
Exception Caught!

(1 row(s) affected)

There are very interesting things to note here. 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 (well, 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, all versions of SQL Server starting with 2005 offer 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
----------------

(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.

Next up: when exactly to use TRY/CATCH.