TSQL: Error handling with Try Catch from a .NET perspective

Starting with SQL 2005, we can handle unexpected situations in our TSQL scripts/procedures/functions in an structured manner using the famous Try/Catch methodology, similar to what we are used to in our object oriented programming languages (C#, Java, etc). This post will describe certain similarities and how to enjoy the features that look absent at the first look. The MSDN document that describes error handling in TSQL in much detail is: Using TRY…CATCH in Transact-SQL

Let’s first look at the syntax for try catch blocks in Transact SQL:

BEGIN TRY
    -- Some code that may throw an exception
END TRY
BEGIN CATCH
    -- code to handle exceptions
END CATCH

Here’s a typical usage of try catch statements in C#:

try
{
    // Some code that may throw an exception
}
catch (FormatException ex)
{
    // some code that handles the FormatException
}
catch (InvalidOperationException ex)
{
    // some code that handles the InvalidOperationException 
}
catch (Exception ex)
{
    // some code that handles all other exception
}
finally
{
    //any clean up code. This portion is executed regardless an exception is thrown on not
}

So the syntax and usage of exception handling in both C# and SQL is much similar. The SQL version, however, seems to miss some of the features so let’s see how we can overcome these in TSQL.

Multiple catch blocks for different exceptions
In .NET, a catch block can specify an exception type to catch. With this, the multiple catch blocks are evaluated from top to bottom, and the first catch block that matches the type of exception thrown is executed. Such behavior can be achieved in TSQL by looking at the Error_Number() and other related functions in the catch block. This MSDN document describes this: Retrieving Error Information in Transact-SQL

Support for finally
Finally blocks are typically used for clean up in .NET applications and hence they may not be needed in most cases when dealing with TSQL. However, for a similar functionality, we can write the code outside the try catch blocks, thus executing the code regardless an error occurred or not. This work around can work in most of the scenarios.

Throwing exceptions manually
In .NET, we can manually throw exceptions using the throw new MyException(…) syntax. The TSQL equivalent is the RaiseError statement that is described in this MSDN Document: Using RAISERROR

A last note
Finally, note that not “ALL” errors can be handled using the Catch block. Each error message in SQL Server has an associated severity level that ranges from 0 through 25. Errors with severity less than or equal to 10 are considered warnings and ignored, while errors that cause the Database Engine to close the connection and have severity level of 20 or above can not be handled. For more info, see Database Engine Error Severities

Advertisements

2 Responses to “TSQL: Error handling with Try Catch from a .NET perspective”

  1. DotNetShoutout Says:

    TSQL: Error handling with Try Catch from a .NET perspective « Mehroz’s Experiments…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. Diandio Says:

    Better Automation Programs At Cheap Price : kingsbot.tk


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: