Behaviour of RAISERROR within a TRY/CATCH within a loop

Question

I have just encountered a section of SQL that is behaving not as I would expect (See below for a distilled version of the SQL in question that demonstrates the problem pattern).

(This is on SQL Server 2008 R2 SP2 64bit)

The 'do some work here' part raises an error and triggers the error handling in the CATCH block. The error number is 515 (attempt to insert a null into a non-nullable column), hence RAISERROR is reporting the error, but then the loop continues, attempts the work again, throws an error, etc. in an infinite loop.

I would expect RAISERROR to cause execution to exit the loop. What is going on here?

Thanks

WHILE (@Applied <> 1)
BEGIN

    BEGIN TRY

        -- === Do some work here ===

        -- Successfully applied
        SET @Applied = 1;

    END TRY
    BEGIN CATCH

        -- Save the error details
        SELECT
            @ErrorNumber = ERROR_NUMBER(),
            @ErrorMessage = ERROR_MESSAGE(), 
            @ErrorSeverity = ERROR_SEVERITY(), 
            @ErrorState = ERROR_STATE();

        -- Test for a deadlock or uncommittable transaction
        IF (@ErrorNumber = 1205 OR @ErrorNumber = 3930)
            -- Sleep for 5 seconds
            WAITFOR DELAY '00:00:05';
        ELSE
            RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

    END CATCH
END

Solomon Rutzky · Answer 1 · 2015-01-07T16:01:06.290

RAISERROR does not typically stop execution. THROW does, but that was introduced in SQL 2012.

Put a RETURN; or RETURN -1; after the RAISERROR.

The following is taken from the "Remarks" section of the MSDN page for RAISERROR:

When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:

Outside the scope of any TRY block.

With a severity of 10 or lower in a TRY block.

With a severity of 20 or higher that terminates the database connection.

Below is a simple test of the behavior. If running on a version of SQL Server prior to 2012, comment out or remove the line with THROW.

RAISERROR(N'This is from RAISERROR - Severity 16', 16, 1);
PRINT N'----- 1';

;THROW 50505, N'This is from THROW', 1;
PRINT N'----- 2';

GO

PRINT N'----- 3';

-- WITH LOG needed for severity > 18
RAISERROR(N'This is from RAISERROR - Severity 20', 20, 1) WITH LOG;
PRINT N'----- 4';

GO

PRINT N'----- 5';

The above test returns the following in the "Messages" tab:

Msg 50000, Level 16, State 1, Line 2
This is from RAISERROR - Severity 16
----- 1
Msg 50505, Level 16, State 1, Line 5
This is from THROW
----- 3
Msg 2745, Level 16, State 2, Line 4
Process ID 54 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 4
This is from RAISERROR - Severity 20
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

And in the bottom, left corner of SSMS it will indicate "Disconnected".

Behaviour of RAISERROR within a TRY/CATCH within a loop

1 Answers1

Linked