Recording SQL Server call stack when reporting errors

Question

This is a follow up to the question Nested stored procedures containing TRY CATCH ROLLBACK pattern?

In the catch block I use a stored procedure to report (reraise) the error by reading from ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE(), etc. As described here I also have a check so that it can determine if the error has already been rethrown (this happens with nested stored procedures as the error information is passed down through each TRY CATCH block).

What I would like to do, either directly in 'ReportError', or indirectly with my pattern (as described in the first question), is record a stack trace - so when ReportError detects that it is receving an error thrown by itself, it appends the next level of the stack to the error message. This would help me avoid cases where I see an error message coming from some little utility stored procedure, without any way of knowing what called it. If I try doing this directly in ReportError it fails, since the rethrown error reports itself as coming from ReportError - only the original error is visible.

Is there some way for ReportError to perform a stack trace in SQL Server, without passing an argument to every single stored procedure, and without manually maintaining such a trace with #temp table? Basically I want a recursive call of ERROR_PROCEDURE() and ERROR_LINE().

Answer 1

Ok, I'll add our error handling back in :-)

The ERROR_%() functions are visible to the scope of the CATCH block. This means you can use them in a stored proc or function call in each CATCH block

And with nested stored procs, it's useful to know what caused the error and what's logging the error

...
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    EXEC dbo.MyExceptionHandler @@PROCID, @errmsg OUTPUT;
    RAISERROR (@errmsg, 16, 1);
END CATCH

---with this handler (cut down version of ours)
CREATE PROCEDURE dbo.MyExceptionHandler
    @CallerProcID int,
    @ErrorMessage varchar(2000) OUTPUT
WITH EXECUTE AS OWNER --may be needed to get around metadata visibility issues of OBJECT_NAME
AS
SET NOCOUNT, XACT_ABORT ON;

BEGIN TRY
    SET @ErrorMessage = --cutdown
            CASE
                WHEN @errproc = @callerproc THEN        --Caller = error generator
                        --build up stuff

                ELSE    --Just append stuff             --Nested error stack
            END;

    IF @@TRANCOUNT = 0
        INSERT dbo.Exception (Who, TheError, WhatBy, LoggedBy)
        VALUES (ORIGINAL_LOGIN()), RTRIM(ERROR_MESSAGE()), ERROR_PROCEDURE(), OBJECT_NAME(@CallerProcID));
END TRY
BEGIN CATCH
   --and do what exactly?
END CATCH
GO

This is the basic idea anyway: each CATCH block is simple, the work goes on in the error handler. Eg append ERROR_NUMBER() if you want to