August 7, 2009 at 9:39 am
We have a procedure that controls the execution of several other procedures within a transaction. The master proc calls each sub-proc and checks @@ERROR to see if an error occured. I'm writing a new sub-proc and using the RAISERROR and RETURN (return code) inside a catch block, but the master proc isn't branching into it's error handler as expected. My hunch is that the existing code isn't checking the return codes properly in the master proc, but an error is thrown and so it still works. But now that I'm using a try catch, the proc isn't causing an error to propagate up to the master.
Here's the code in the master proc:
begin
--Run subproc
exec spNewSubProc @params
if @@ERROR<>0 goto OnError
end
Here's existing error handling in the sub procs (using the @@ERROR check and GOTO's):
ErrorHandler:
BEGIN
EXEC sp_addmessage 50001, 16, @ErrorMessage, 'English', 'False', 'REPLACE'
RAISERROR (50001, 16, 1)
Return (16)
END
EndofJob:
Return (0)
And here's the updated error handling with the catch block:
begin try
--bunch of stuff
--return with no errors
return (0)
end try
begin catch
declare @ErrorMessagenvarchar(255);
select @ErrorMessage = 'An error occured in ' + ERROR_PROCEDURE() + ' at line ' + cast(ERROR_LINE() as nvarchar(10))
exec sp_addmessage 50001, 16, @ErrorMessage, 'English', 'False', 'REPLACE'
raiserror (50001, 16, 1)
return (16)
end catch
So, the resulting behavior is that an error is thrown and logged as expected, but the calling proc doesn't know that an error occured. And it appears the "return (nn)" statements aren't being used by the caller as intended.
Thanks in advance for the help, please let me know if I can provide any more information!
Taylor
August 7, 2009 at 10:25 am
Either use exception handling in the calling procedure or try the following:
--Run subproc
DECLARE @err int
EXEC @err = spNewSubProc @params
IF @err 0
GOTO OnError
August 7, 2009 at 10:52 am
Ahh, that looks like what the intent was initially. The calling procedure is a monster (2000+ lines) so I don't want to touch it more than I have to. I'll give your suggestion a shot. Thanks!
Taylor
August 7, 2009 at 10:56 am
Your master proc will catch the error if you raise an error in the child proc. You'll just need your master's catch block to capture and raise the actual error from the child proc instead of a generic error from the master. Make sense?
I use a SP designed to re-throw the error. All of my SPs use the rethrow error proc in the catch block. It works for raising an error in the called proc or any nested procs.
August 7, 2009 at 12:34 pm
Makes total sense. The issue is that the master isn't using a try...catch, and the error isn't being trapped with the @@ERROR 0 check. It seems like whatever error occured was caught and handled successfully, thus the sub proc isn't in an error state when control returns to the master.
August 7, 2009 at 1:41 pm
Well, Ken covered the other option, capture the return value of the executing Proc, but I would take it a step further and code it like this:
--Run subproc
DECLARE @err int
EXEC @err = spNewSubProc @params
IF COALESCE(NULLIF(@err,0),@@ERROR) 0
GOTO OnError
That way, you are checking the return value from the SP (are your child SPs returning a non-zero value for errors?) and the @@ERROR property.
By the way, you are using SQL Server 2005, why not just change your master to use a TRY/CATCH block? It's a simple code change and less GOTO code to hastle with.
August 7, 2009 at 3:44 pm
That would be my preference, but it's a battle I'll fight another day! 🙂
Thanks for your help!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply