sql-serversql-server-2019

Why does normal execution resume after Raiserror in Catch block


When I execute the stored procedure shown below, I get the output

error outer - INTERESTING THIS FIRES

I do not get

error inner - INTERESTING THIS FIRES

Why is this?

/*
EXEC SP_RAISERROR_TRYCATCHTEST3
*/
 
CREATE PROCEDURE [dbo].[SP_RAISERROR_TRYCATCHTEST3]
AS
    SET NOCOUNT ON
 
    DECLARE @ERRMSG VARCHAR(200)
 
    BEGIN TRY
        SELECT 'IN 1ST TRY IE OUTER'
        PRINT 'IN 1ST TRY IE OUTER'
 
        BEGIN TRY
            SELECT 'IN 2ND TRY IE INNER'
            PRINT 'IN 2ND TRY IE INNER'
 
            RAISERROR('THROWN FROM INNER', 16, 1)
                                    
            SELECT 'ENDING NORMAL IN 2ND TRY IE INNER'
            PRINT 'ENDING NORMAL IN 2ND TRY IE INNER'
        END TRY
        BEGIN CATCH
            SET @ERRMSG = 'CAUGHT IN INNER CATCH. ERRMSG IS: ' + ERROR_MESSAGE()
            SELECT @ERRMSG AS ERRMSG
            PRINT @ERRMSG
 
            RAISERROR ('Totally New Throw from INNER CATCH', 16, 1)

            SELECT 'Inner Catch- INTERESTING THIS FIRES'
            PRINT 'Inner catch - INTERESTING THIS FIRES'
        END CATCH
 
        SELECT 'ENDING NORMAL IN 1ST TRY IE OUTER'
        PRINT 'ENDING NORMAL IN 1ST TRY IE OUTER'
    END TRY
    BEGIN CATCH
        SET @ERRMSG = 'CAUGHT IN OUTER CATCH. ERRMSG IS: ' + ERROR_MESSAGE()

        SELECT @ERRMSG AS ERRMSG
        PRINT @ERRMSG
 
        RAISERROR ('Totally New Throw from Outer CATCH', 16, 1)
 
        SELECT 'error outer - INTERESTING THIS FIRES'
        PRINT 'error outer - INTERESTING THIS FIRES'
    END CATCH
 
    SELECT 'SP END'
    PRINT 'SP END'

Results:

IN 1ST TRY IE OUTER
IN 2ND TRY IE INNER
CAUGHT IN INNER CATCH. ERRMSG IS: THROWN FROM INNER
CAUGHT IN OUTER CATCH. ERRMSG IS: Totally New Throw from INNER CATCH
Msg 50000, Level 16, State 1, Procedure SP_RAISERROR_TRYCATCHTEST3, Line 68 [Batch Start Line 15]  
Totally New Throw from Outer CATCH
error outer - INTERESTING THIS FIRES
SP END

Solution

  • When RAISERROR or THROW occurs inside a TRY block execution to shifts to the CATCH block.

    When RAISERROR or THROW occurs without a TRY block execution continues with next statement, or the batch is aborted (depending on the error and the XACT_ABORT setting).

    TSQL doesn't really have exceptions, and TRY/CATCH is an add-on, so the behavior is a bit complex.

    but I DO get "error outer - INTERESTING THIS FIRES", even though both Print/select statements take place after the Raiserrors in each Catch block.

    Because the line

    RAISERROR ('Totally New Throw from Outer CATCH', 16, 1)

    is not in a TRY block, so it doesn't have a CATCH block to jump to.

    See https://www.sommarskog.se/error-handling-I.html

    NB ... to add a bit more details: The first raiserror causes execution to leave the catchblock because the raiserror statement actually took place inside a try block [ie the outer one ] regardless of whether the raiserror was inside a catchblock (This also appplies if there was an outer try catch block in a calling stored-proc higher up in call stack.); execution continued in the second scenario because there was no outer try block.