Sql-server – Behaviour of RAISERROR within a TRY/CATCH within a loop

raiserrorsql-server-2008-r2

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

Best Answer

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

Related Solutions

Sql-server – Make SQL Server Agent retry failed script with Try/Catch

Did you use RAISERROR with severity 16?
This is "user defined error" and should be picked up by SQL Server agent

Did you set the job step to retry (Retry Interval and Retry Attempts)

Finally, is the error terminating (severity 20) such that the CATCH block isn't hit?

Sql-server – Transaction and Try-catch in SQL Server Job

I would rather recommend a pattern like the one from Exception Handling and Nested Transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end

This pattern checks the XACT_STATE() in the catch block to guard against uncommittable transactions:

Uncommittable Transactions and XACT_STATE
If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This indicates that an uncommittable transaction was detected and rolled back.

Your code is checking for @@TRANCOUNT in places where it cannot be 0, it uses a mixture of informational PRINT messages and SELECT result sets for communicating success, it does not handle errors that are recoverable. Ideally the exceptions should propagate to the client, in this case to the Agent job (ie. your catch should re-raise).

Best Answer

Related Solutions

Sql-server – Make SQL Server Agent retry failed script with Try/Catch

Sql-server – Transaction and Try-catch in SQL Server Job

Related Question