Error handling in container procedures

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?

I use this pattern proposed in Exception Handling and Nexted Transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end

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.

StackExchange Database Administrators Q#17633, answer score: 3