I'm aware of and use the techniques in this question.
In my environment we have a lot of container procedures that call subprocedures, that may call other procedures, ad infinitum.
For me personally, I normally use TRY/CATCH
in each level of the procedures. This will sometimes result in multiple duplicate error messages but it's clear what happened and in what proc.
I have some coworkers who take a different approach. Namely:
– Have each stored proc issue a return code
– Evaluate the return code for each stored proc after execution
– Raiserror in the outermost proc if a bad return code is received
I believe they do this so they can get the actual line number from the erroring procedure (instead of having it return the line number of the RAISERROR
in the CATCH
block).
To me this seems like it would be harder to implement and maintain, but I'm not an expert on SQL Server error handling, so I thought I would bring it up here.
Is there any material advantage to either method for handling errors in container procedures?
Best Answer
I use this pattern proposed in Exception Handling and Nexted Transactions:
This not only raises, but also can rollback to savepoint, if possible. This is very convenient in batch processing, the batch is not lost if a row has an error.
I never ever use return codes in stored procs. Returns codes beg to be ignored. I acknowledge the problem of nested errors as an annoyance, but with SQL Serevr 2012 you have the simple
THROW;
(no other arguments) to re-raise the original exception.