SQL TRY Error not CATCHING

Question

Have a stored procedure and have wrapped the code in the format

BEGIN TRY
    BEGIN TRAN
        ...code
    COMMIT
END TRY

BEGIN CATCH
    ROLLBACK
    ...code
END CATCH

Which works fine until I started doing some testing for various errors to make sure they were being entered into my log table correctly.

However, when I run this, it fails, and does not rollback and requires a manual rollback. It's like the code does not realise that it is in a TRY block.

Any ideas? (Code below, hoping it can be recreated on someone else's system and not some bizarre way on how the systems where I am are configured)

BEGIN
     SET NOCOUNT ON

    BEGIN TRY

        BEGIN TRAN

            --------------------------------------------------------------------- 
            -- Create test table
            ---------------------------------------------------------------------
            IF OBJECT_ID('tempdb..#DateOfBirth') IS NOT NULL DROP TABLE #DateOfBirth
            CREATE TABLE #DateOfBirth
            (
                DateOfBirth DATE
            )
            INSERT INTO #DateOfBirth
            VALUES
                ('1984-12-09')
                ,('1977-12-09')
                ,('2015-03-12')
                ,('1967-01-15')

            --------------------------------------------------------------------- 
            -- Date Of Birth
                -- This Insert errors
            ---------------------------------------------------------------------
            IF OBJECT_ID('tempdb..#DOB') IS NOT NULL DROP TABLE #DOB
            CREATE TABLE #DOB
            (
                groupID INT IDENTITY(1,1)
                , DateOfBirth INT -- Data Type mismatch
            )

            INSERT INTO #DOB
            SELECT DateOfBirth
            FROM #DateOfBirth

        COMMIT
    END TRY

    BEGIN CATCH

        PRINT 'Rollback'

        ROLLBACK

        DECLARE @ErrorMessage   NVARCHAR(4000) = ERROR_MESSAGE(),
                @ErrorState     INT = ERROR_STATE(),
                @ErrorSeverity  INT = ERROR_SEVERITY();

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

    END CATCH

END
GO

Dan Guzman · Accepted Answer

Compilation errors within the same scope of the CATCH block cannot be caught. To wit, add a PRINT before each statement:

PRINT 'BATCH STARTED';
BEGIN
    SET NOCOUNT ON

    BEGIN TRY

        PRINT 'BEGIN TRAN';
        BEGIN TRAN;

        IF OBJECT_ID('tempdb..#DateOfBirth') IS NOT NULL DROP TABLE #DateOfBirth;

        PRINT 'CREATING #DateOfBirth';
        CREATE TABLE #DateOfBirth
        (
            DateOfBirth DATE
        );

        PRINT 'INSERTING INTO #DateOfBirth';
        INSERT INTO #DateOfBirth
        VALUES
            ('1984-12-09')
            ,('1977-12-09')
            ,('2015-03-12')
            ,('1967-01-15')

        IF OBJECT_ID('tempdb..#DOB') IS NOT NULL DROP TABLE #DOB;

        PRINT 'CREATING #DOB';
        CREATE TABLE #DOB
        (
            groupID INT IDENTITY(1,1)
            , DateOfBirth INT -- Data Type mismatch
        );

        PRINT 'INSERTING INTO #DOB';
        INSERT INTO #DOB
        SELECT DateOfBirth
        FROM #DateOfBirth;

        PRINT 'COMMIT';
        COMMIT;
    END TRY

    BEGIN CATCH

        PRINT 'ROLLBACK';

        ROLLBACK;

        DECLARE @ErrorMessage   NVARCHAR(4000) = ERROR_MESSAGE(),
                @ErrorState     INT = ERROR_STATE(),
                @ErrorSeverity  INT = ERROR_SEVERITY();

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

    END CATCH;

END;
GO

When this batch is initially executed on a new session, the resultant messages are:

BATCH STARTED
BEGIN TRAN
CREATING #DateOfBirth
INSERTING INTO #DateOfBirth
CREATING #DOB
INSERTING INTO #DOB
Msg 206, Level 16, State 2, Line 36
Operand type clash: date is incompatible with int

The important point is that this error occurs during statement compilation, not execution. Because the temp tables do not exist when the batch is compiled, compilation of the statements referencing those tables is deferred until the statements are run. Any errors that occur during the compilation can't be caught in by the CATCH block in the same scope.

The compilation error can be caught if you execute the statements using dynamic SQL so that the compilation occurs in a different scope:

PRINT 'BATCH STARTED';
BEGIN
    SET NOCOUNT ON

    BEGIN TRY

        PRINT 'BEGIN TRAN';
        BEGIN TRAN;

        EXECUTE('
        IF OBJECT_ID(''tempdb..#DateOfBirth'') IS NOT NULL DROP TABLE #DateOfBirth;

        PRINT ''CREATING #DateOfBirth'';
        CREATE TABLE #DateOfBirth
        (
            DateOfBirth DATE
        );

        PRINT ''INSERTING INTO #DateOfBirth'';
        INSERT INTO #DateOfBirth
        VALUES
            (''1984-12-09'')
            ,(''1977-12-09'')
            ,(''2015-03-12'')
            ,(''1967-01-15'')

        IF OBJECT_ID(''tempdb..#DOB'') IS NOT NULL DROP TABLE #DOB;

        PRINT ''CREATING #DOB'';
        CREATE TABLE #DOB
        (
            groupID INT IDENTITY(1,1)
            , DateOfBirth INT -- Data Type mismatch
        );

        PRINT ''INSERTING INTO #DOB'';
        INSERT INTO #DOB
        SELECT DateOfBirth
        FROM #DateOfBirth;

        PRINT ''COMMIT'';
        COMMIT;
        ');


    END TRY

    BEGIN CATCH

        PRINT 'ROLLBACK';

        ROLLBACK;

        DECLARE @ErrorMessage   NVARCHAR(4000) = ERROR_MESSAGE(),
                @ErrorState     INT = ERROR_STATE(),
                @ErrorSeverity  INT = ERROR_SEVERITY();

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

    END CATCH;

END;
GO

In this case, the CATCH block is entered and the transaction rolled back:

BATCH STARTED
BEGIN TRAN
CREATING #DateOfBirth
INSERTING INTO #DateOfBirth
CREATING #DOB
INSERTING INTO #DOB
ROLLBACK
Msg 50000, Level 16, State 2, Line 58
Operand type clash: date is incompatible with int

BTW, I strongly recommend you specify 'SET XACT_ABORT ON' to help ensure the transaction is rolled back after errors in cases where the CATCH block is not executed (e.g. a client query timeout error).

SQL TRY Error not CATCHING

Answers (2)

Related Questions