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
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.