Recording Sql Server Call Stack When Reporting Errors

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 rea

Solution 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() <>0AND@starttrancount=0ROLLBACK TRANSACTION
    EXEC dbo.MyExceptionHandler @@PROCID, @errmsg OUTPUT;
    RAISERROR (@errmsg, 16, 1);
END CATCH

---with this handler (cut down version of ours)CREATEPROCEDURE dbo.MyExceptionHandler
    @CallerProcIDint,
    @ErrorMessagevarchar(2000) OUTPUT
WITHEXECUTEAS OWNER --may be needed to get around metadata visibility issues of OBJECT_NAMEASSET NOCOUNT, XACT_ABORT ON;

BEGIN TRY
    SET@ErrorMessage=--cutdownCASEWHEN@errproc=@callerprocTHEN--Caller = error generator--build up stuffELSE--Just append stuff             --Nested error stackEND;

    IF @@TRANCOUNT=0INSERT 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

Solution 2:

A limited answer to this would be to pass OBJECT_NAME(@@PROCID) to the ReportError procedure - when ReportError detects that it is receving a recursive error (an error thrown by itself), it can use this value and append it to the error message, providing a partial stack trace (stack trace won't have line numbers except for the first element)