Error Handling - Determine whether sp_executesql was the source

Question

When an error message is returned by sp_executesql, is there any built-in error handling method/mechanism that can be used to identify that this error was returned by this procedure vs. directly from the containing script?

In the case of the below, the error details identify the error as occurring on line 1 but they don't indicate that the line 1 being referred to is not line 1 from the script itself but rather line 1 in the query passed by the script to sp_executesql.

I'm looking for some way to identify the source so that I can log it accordingly. I'd like to log something like Script x - Call to inner query errored on that query's line 1 instead of the generic (and misleading) Script x errored on line 1.

Demo

-- do other things first

BEGIN TRY  
    EXECUTE sp_executesql @stmt = N'SELECT 1/0';
END TRY
BEGIN CATCH  
    SELECT ERROR_NUMBER() AS ErrorNumber
         ,ERROR_SEVERITY() AS ErrorSeverity
         ,ERROR_STATE() AS ErrorState
         ,ERROR_PROCEDURE() AS ErrorProcedure
         ,ERROR_LINE() AS ErrorLine
         ,ERROR_MESSAGE() AS ErrorMessage
END CATCH; 

Returns:

ErrorNumber ErrorSeverity ErrorState  ErrorProcedure 
----------- ------------- ----------- ---------------
8134        16            1           NULL           

Answer 1

Unfortunately, the call stack isn't available with T-SQL error handling. Consider upvoting this feature request to facilitate capturing T-SQL stack details.

The example below uses a nested TRY/CATCH to raise a user-defined error (message number 50000) when the inner script errors, capturing the available details along with context description ("inner script"). When errors occur in the outer script, the original error is simply re-thrown. Absence of a context and a system error number indicates the outermost script erred, although you could build and raise a user-defined error there instead, including the outer script context description.

BEGIN TRY
    BEGIN TRY

        EXECUTE sp_executesql @stmt = N'SELECT 1/0;';
    END TRY
    BEGIN CATCH  

        DECLARE
             @ErrorNumber int
            ,@ErrorMessage nvarchar(2048)
            ,@ErrorSeverity int
            ,@ErrorState int
            ,@ErrorLine int;

        SELECT
             @ErrorNumber =ERROR_NUMBER()
            ,@ErrorMessage =ERROR_MESSAGE()
            ,@ErrorSeverity = ERROR_SEVERITY()
            ,@ErrorState =ERROR_STATE()
            ,@ErrorLine =ERROR_LINE();

        RAISERROR('Error %d caught in inner script at line %d: %s'
            ,@ErrorSeverity
            ,@ErrorState
            ,@ErrorNumber
            ,@ErrorLine
            ,@ErrorMessage);

    END CATCH; 
END TRY
BEGIN CATCH  

    THROW;

END CATCH; 
GO