CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Simon Elms
Simon Elms

Reputation: 19668

When does RAISERROR fire in a stored procedure?

I've got a stored procedure that contains a try-catch block. In the catch block I call raiserror() to rethrow the error with some context.

I was expecting that if an error occurred the raiserror() would be called and execution would immediately return from the stored procedure to the calling code. However, this doesn't appear to be the case. It looks like execution of the stored procedure continues until it hits a return statement, then the raiserror() takes effect.

Is this correct - that raiserror() won't have an effect until return is called or the end of the stored procedure is reached?

I'm using SQL Server 2012.

EDIT: in reply to request for details of the stored procedure, here's the relevant snippet of code:

DECLARE @ErrMsg VARCHAR(127) = 'Error in stored procedure ' + OBJECT_NAME(@@PROCID) + ': %s';

declare @UpdateDateRecordCount table (LastUpdated datetime, NumberRecords int);
begin try;
    insert into @UpdateDateRecordCount (LastUpdated, NumberRecords)
    exec sp_ExecuteSql 
        @UpdateCountQuery, 
        N'@LastUpdated datetime', 
        @LastUpdated = @LastUpdated;

    if @@rowcount <= 0
    begin;
        return 0;
    end; 
end try
begin catch;
    declare @InsertError varchar(128) = 'Error getting updated date record count: ' 
        + ERROR_MESSAGE();
    RAISERROR (@ErrMsg, 16, 1, @InsertError);
end catch;

-- Attempt to loop through the records in @UpdateDateRecordCount...

The @UpdateCountQuery argument will be set to something like:

N'select LastUpdated, count(*) from dbo.Part where LastUpdated > @LastUpdated group by LastUpdated;'

Upvotes: 2

Views: 13195

Answers (3)

M&#246;oz
M&#246;oz

Reputation: 863

As I understand it, if you want the execution to stop, you need to raise the error within the TRY block, and then raise the error again in your CATCH block this will make sure that the error is "raised" to the caller.

Or you could add a RETURN statement after your RAISERROR statement in the CATCH block. This will exit the procedure and return to the caller.

Also, as suggested by MSDN you should try to use the THROW statement instead of RAISERROR since it (the RAISERROR) will be phased out.

Upvotes: 4

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

That's not how it works in T-SQL. Nothing in the documentation for TRY...CATCH or RAISERROR specifies any special cases that would override:

When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement. Errors trapped by a CATCH block are not returned to the calling application. If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR and PRINT statements.

If you want the stored proc to exit, you need a RETURN statement as well.

Upvotes: 3

OperationNewDay
OperationNewDay

Reputation: 143

It depends on the severity level that you use. There's a lot more information in the below link:

http://technet.microsoft.com/en-us/library/ms178592.aspx

But to quote the article:

The errors generated by RAISERROR operate the same as errors generated by the Database Engine code. The values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions. 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...

So if your severity level is 11 or higher then the control will be immediately transferred to the CATCH block.

The below example shows a severity level of 16:

RAISERROR ('Error raised in TRY block.', -- Message text.
16, -- Severity.
1 -- State.
);

Upvotes: 0

Related Questions