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 yourCATCH
block this will make sure that the error is "raised" to the caller.Or you could add a
RETURN
statement after yourRAISERROR
statement in theCATCH
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 (theRAISERROR
) will be phased out.