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
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?