As we continue our journey through the “Underappreciated features of SQL Server”, this week are are looking at a few of the T-SQL enhancements that the community felt did not get the deserved attention. This was in response to Andy Warren’s editorial of the same name on SQLServerCentral.com.

Today, we will look at exception handling in T-SQL using the TRY…CATCH statements introduced since SQL Server 2005. Because this is now almost 6 years old, I was a bit surprised to see this in the list of underappreciated features. Hence, while I will touch upon the essential points, I will provide pointers that would help you to get started on the use of TRY…CATCH and then explore a new related feature introduced in SQL 11 (“Denali”). I am posting this on a Thursday, because I will be covering the exception handling options from SQL 7/2000 onwards and also so that it the reader gets some time to experiment with the various options presented here over the week-end.

Exception handling in T-SQL – before SQL Server 2005

Before SQL Server 2005, exception handling was very primitive, with a handful of limitations. Let’s see how exception handling was done in the days of SQL Server 7/2000:

BEGIN TRANSACTION ExceptionHandling
   DECLARE @ErrorNum INT
   DECLARE @ErrorMsg VARCHAR(8000)

   --Divide by 0 to generate the error
   SELECT 1/0

   -- Error handling in SQL 7/2000 
   -- Drawback #1: Checking for @@Error must be done immediately after execution fo a statement
   SET @ErrorNum = @@ERROR
   IF @ErrorNum <> 0 
   BEGIN
      -- Error handling in SQL 7/2000 
      -- Drawback #2: When returning an error message to the calling program, the Message number,
      --              and location/line# are no longer same as the original error.
      --              Explicit care must be taken to ensure that the error number & message
      --              is returned to the user.
      -- Drawback #3: Error message is not controlled by the application, but by the T-SQL code!
      -- (Please note that there is only one 'E' in RAISERROR)
      SELECT @ErrorMsg = description FROM sysmessages WHERE error = @ErrorNum
      RAISERROR ('An error occured within a user transaction. Error number is: %d, and message is: %s',16, 1, @ErrorNum, @ErrorMsg) WITH LOG
      ROLLBACK TRANSACTION ExceptionHandling
   END

IF (@ErrorNum = 0)
   COMMIT TRANSACTION ExceptionHandling

While a revolutionary method in those days, to our “developed” eyes, a couple of issues that jump right as us:

1. You cannot monitor a batch for Exceptions/error, i.e. you cannot be sure that a particular block of code will be able to “catch” all errors that might happen in another block of code somewhere else
2. The checking for the return value of the @@ERROR system function is where most mistakes happen. This has to be checked immediately after the execution of the T-SQL statement that you want to monitor
3. You can inform the calling program/application about the error, however the error number, error description and originating line number change – the error number is now that of RAISERROR() and the error message is custom. The originating line number is that of the line from where the call to RAISERROR is made. When used with the “WITH LOG” option, this is big drawback when doing a post-error analysis
4. Because the error message is custom, the calling application has no control over the nature of the message (e.g. sometimes, we might want to customize the message text that is displayed to the users) – this cannot be done

Clearly, with the advent of nested operations and multi-tier architecture and patterns, this had a lot of drawbacks, and the world was in need of something new.

Exception handling in T-SQL – SQL Server 2005/2008

SQL Server 2005 saw a major change in the history and architecture of Microsoft SQL Server. SQL Server now started using the Visual Studio platform and was now Microsoft .NET framework based. One of the things that this move gave to SQL Server was the fact that it could now leverage the built-in TRY…CATCH functionality provided by the framework. To know more about this functionality, please refer an article from our esteemed MVP, Mr. Pinal Dave (blog).

Let’s see how our code would look and work when we implement TRY…CATCH blocks:

BEGIN TRANSACTION ExceptionHandling
   -- Error handling in SQL 2005 & up
   -- Monitor an entire batch of queries for errors
   BEGIN TRY
      --Some valid operations here
      SELECT 1/1

      --Divide by 0 to generate the error
      SELECT 1/0

      --Some valid operations here
      SELECT 1/2

      COMMIT TRANSACTION ExceptionHandling
   END TRY
   BEGIN CATCH
      -- Error handling in SQL 2005 & up
      -- Error information can be returned to the calling code as a result set
      SELECT ERROR_NUMBER() AS ErrorNumber
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_STATE() AS ErrorState
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_LINE() AS ErrorLine
            ,ERROR_MESSAGE() AS ErrorMessage;

      -- Or via RAISERROR
      DECLARE @ErrorNumber    INT            = ERROR_NUMBER()
      DECLARE @ErrorMessage   NVARCHAR(4000) = ERROR_MESSAGE()
      DECLARE @ErrorProcedure NVARCHAR(4000) = ERROR_PROCEDURE()
      DECLARE @ErrorLine      INT            = ERROR_LINE()

      RAISERROR ('An error occurred within a user transaction. 
                  Error Number        : %d
                  Error Message       : %s  
                  Affected Procedure  : %s
                  Affected Line Number: %d'
                  , 16, 1
                  , @ErrorNumber, @ErrorMessage, @ErrorProcedure,@ErrorLine)
       
      IF @@TRANCOUNT > 0
         ROLLBACK TRANSACTION ExceptionHandling
   END CATCH

As you can see, most of the problems are now resolved. Microsoft SQL Server provides specific functions that would return the correct information about an error occurring from anywhere within a block of monitored code (i.e. the code in “TRY”). One problem remains however – if your code uses RAISERROR, you would still have the problem of an incorrect error number (if not embedded within the message).

The basic structure and use of TRY…CATCH is quite simple as demonstrated above. There is much more to TRY…CATCH, which I will leave it upon the reader to explore.

Exception handling in T-SQL – SQL 11 (Code named: “Denali”)

The remaining drawback of RAISERROR mentioned above and the solution provided by Microsoft in it’s upcoming SQL Server release (SQL 11 – Code named: “Denali”) is handled very well by the renowned MVP, Mr. Jacob Sebastian (blog) here.

SQL 11/”Denali” comes with a new THROW command that helps the T-SQL code to re-raise the same error that has been encountered in a monitored block of code. While I was reading Jacob’s article, a question cropped up in my mind as to whether we could re-raise errors in a nested hierarchy. The concept of “inner exceptions” is still foreign to SQL Server, but THROW does allow for nesting. Please find below the script I developed to experiment with nesting exception-handling:

IMPORTANT NOTE: The THROW command must be preceded with a statement ending in a semi-colon (;)

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'InnerProc' AND type = 'P')
    DROP PROCEDURE InnerProc
GO

CREATE PROCEDURE InnerProc
AS
BEGIN
    BEGIN TRANSACTION ExceptionHandling
       BEGIN TRY
          PRINT 'In the TRY block of the Inner Procedure...'
          SELECT 1/1

          RAISERROR('An error occured in the Inner procedure.',17,1)  --Line #10 considering CREATE PROC...as Line #1

          COMMIT TRANSACTION ExceptionHandling
       END TRY
       BEGIN CATCH
          SELECT ERROR_NUMBER() AS ErrorNumber
                ,ERROR_SEVERITY() AS ErrorSeverity
                ,ERROR_STATE() AS ErrorState
                ,ERROR_PROCEDURE() AS ErrorProcedure
                ,ERROR_LINE() AS ErrorLine
                ,ERROR_MESSAGE() AS ErrorMessage;

          IF @@TRANCOUNT > 0
             ROLLBACK TRANSACTION ExceptionHandling

          PRINT 'Throwing error from the CATCH block of the INNER Procedure...';   
          --Preceding statement MUST be a semi-colon ';'
          THROW
       END CATCH
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'OuterProc' AND type = 'P')
    DROP PROCEDURE OuterProc
GO
CREATE PROCEDURE OuterProc
AS
BEGIN
    BEGIN TRY
        PRINT 'In the TRY block of the Outer Procedure...'
        EXEC InnerProc
    END TRY
    BEGIN CATCH
        PRINT 'In the CATCH block of the Outer Procedure...';
        --Preceding statement MUST be a semi-colon ';'
        THROW
    END CATCH
END
GO

--Executing the outer procedure
EXEC OuterProc

Edit (October 15, 2011): You can explore the differences between THROW and RAISEERROR in my post here – Sunset for RAISERROR and sunrise for THROW – SQL 11 (“Denali”) (End Edit)

Phew!…That was a long one. I hope that you now have a good overview of the powerful exception handling that Microsoft SQL Server provides. In the next release of your product, ensure that all our T-SQL code is protected from unhandled exceptions.

If you want to read more on exception handling, you can refer Erland Sommarskog’s (blog) post here.

If you have any good exception handling tips to share, do let us know by leaving your feedback.

Until we meet next time,

Be courteous. Drive responsibly.