The Basic TRY..CATCH

Have you written a TRY..CATCH statement in T-SQL? I hadn’t done it for most of my career, since the construct hadn’t existed. As a result, my code over the years is littered with catching @@error in a variable and then acting on that result. 

However I’m trying to do better, and when I went to write one recently, I realized that I wasn’t doing it enough as I needed to check some syntax. Here’s a short post to try and capture that information and burn it into my brain.

The Syntax

The basic syntax is this:

BEGIN TRY

— do some work here.

END TRY

BEGIN CATCH

— error handling code here.

END CATCH

This almost seems funny as I’d expect a TRY with a BEGIN END block in the SQL language, but this reads better, and I think this is (Syntactically) a better implementation in the language.

Using TRY . . CATCH

The use of this is to do some work in the TRY block (BEGIN TRY..END TRY) and expect it to work. For example, I recently had this:

BEGIN TRY
    SELECT TOP 10
            cs.CustomerID
        ,   cs.LastSale
        ,   cs.Salesman
        ,   CAST(cs.SaleValue AS NUMERIC)
        FROM
            dbo.CustomerSales AS cs;
END TRY

BEGIN CATCH

— CATCH BLOCK

END CATCH

SELECT @@rowcount

The TRY block is the place where I perform some work. If it works as expected, then I just continue on. In this case, this should be a simple query that runs, and when it finishes, the SELECT for the rowcount executes.

However, if some error occurs, execution immediately goes to the CATCH block. In that case, whatever I have in that space will execute and then the execution will continue.

Example

Let’s look at an example of how this works. Here’s my full TRY..CATCH with a few print statements to track the activity.

ALTER PROCEDURE spGetCommission
@userid INT
AS
PRINT ‘Before TRY’;

    BEGIN TRY
        PRINT ‘Start TRY’;
        SELECT TOP 10
                cs.CustomerID
            ,   cs.LastSale
            ,   cs.Salesman
            ,   CAST(cs.SaleValue AS NUMERIC)
            FROM
                dbo.CustomerSales AS cs;
        PRINT ‘End TRY’;
    END TRY
    BEGIN CATCH
        PRINT ‘Start CATCH’;
        THROW 51000, ‘A calculation error occurred’, 1;
        PRINT ‘End CATCH’;
    END CATCH;

PRINT ‘End of proc’;

GO

If I not execute this, with a parameter, I get this:

Before TRY
Start TRY

(0 row(s) affected)
Start CATCH
Msg 51000, Level 16, State 1, Procedure spGetCommission, Line 20
A calculation error occurred

That might not be what you expected. The TRY works as expected, with the error in my query sending execution to the CATCH block, before the final print statement in the TRY block.

However I didn’t get the complete execution of the CATCH block, as the THROW throws an error and completes its execution. If I changed this to not re-throw the error, the final statement executes.

ALTER PROCEDURE spGetCommission

    BEGIN CATCH
        PRINT ‘Start CATCH’;
        PRINT    ‘A calculation error occurred’
        PRINT ‘End CATCH’;
    END CATCH;

PRINT ‘End of proc’;

GO

In this case, I’ll get all my print statements.

Before TRY
Start TRY

(0 row(s) affected)
Start CATCH
A calculation error occurred
End CATCH
End of proc

A basic look at TRY..CATCH, and worth knowing about. I’d suggest you use this in future code, and even refactor code where you can to include this instead of looking at @@error to trap issues.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

2 Responses to The Basic TRY..CATCH

  1. Solomon says:

    Hey there. A few things to note with regards to the CATCH block and THROW:

    1) The THROW command was introduced in SQL Server 2012 (hence it won’t work in 2005 / 2008 / 2008 R2)

    2) Whereas the THROW command terminates the procedure, RAISERROR does not.

    3) If using THROW, just place it at the end of the CATCH block, just prior to “END CATCH”

    4) THROW must be proceeded with a semicolon (in the example code here, the semicolon is at the end of the prior line)

    5) THROW can be called without passing any parameters to, in which case it will re-throw the original error, including the original error number (a value below 50000 which cannot be set manually) which is sometimes nice to have

    6) You should nearly always use either THROW or RAISERROR (doesn’t matter which one) at the end of your CATCH block to let the calling process know that an error occurred. Else your code is swallowing the error as if nothing went wrong.

    • way0utwest says:

      Thanks for the notes. I should have noted number 2 and 3, which can cause issues. #4 isn’t really correct. It’s the previous statement that needs terminating, not the THROW itself requiring a semicolon.

      5 is a good point as well. I tend to re-throw an error many times, but using the existing one from SQL Server is a good idea.

Comments are closed.