Sql – When does RAISERROR fire in a stored procedure

sqlsql serversql-server-2012tsql

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;'

Best Answer

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.

Related Topic