SQL Server Stored Procedures StackTrace - 2016

SQL Server Stored Procedures StackTrace - 2016.sql

/*
	Authors: Matthew Naul, Tim Cartwright
	Purpose: to build a stack trace so that nested stored proces can be followed in the case of an exception.
*/
USE tempdb -- change this to the db of your choice. or put it master and make it a system stored proc
GO


IF OBJECT_ID('dbo.ErrorHandler') IS NULL BEGIN 
  EXEC ('CREATE PROCEDURE dbo.ErrorHandler AS BEGIN SELECT 1; END;');
END
GO

--our error handling procedure
ALTER PROCEDURE dbo.ErrorHandler
    @procName NVARCHAR(128),
    @ErrorMessage NVARCHAR(4000) OUTPUT,
    @ErrorSeverity INT OUTPUT,
    @ErrorState INT OUTPUT
AS
BEGIN
    /* declare sessions keys that are safe for multiple sessions on a single connection */
    DECLARE @call_stack_name NVARCHAR(128) = N'call_stack_' + CAST(@@SPID AS NVARCHAR(20)),
            @errornumber_name NVARCHAR(128) = N'errornumber_' + CAST(@@SPID AS NVARCHAR(20)),
           /* work around for there not being a ERROR_SCHEMA() or ERROR_PROC(@with_schema=1) 
			https://windowsscriptingdotorg.wordpress.com/2011/01/19/errorprocedure-return-schema/
			apparently this works(-ish) in 2017 */
            @proc_schema_name NVARCHAR(128) = N'error_schema_' + CAST(@@SPID AS NVARCHAR(20));

    /* get values out of the session context */
    DECLARE @ErrorNumber INT
        = COALESCE(CAST(SESSION_CONTEXT(@errornumber_name) AS INT), ERROR_NUMBER()),
            /* 2017 started adding schema name to ERROR_PROCEDURE */
            @last_schema sysname = COALESCE(
                                               PARSENAME(ERROR_PROCEDURE(), 2),
                                               CAST(SESSION_CONTEXT(@proc_schema_name) AS sysname),
                                               PARSENAME(@procName, 2)
                                           );

    /* every execution prepends the existing call stack with: CR LF TAB "formated msg" */
    DECLARE @call_stack VARCHAR(4000)
        = CONCAT(
                    CHAR(13) + CHAR(10) + CHAR(9),
                    FORMATMESSAGE(
                                     'Procedure: %s, Line: %d',
                                     CONCAT(QUOTENAME(@last_schema), '.', QUOTENAME(PARSENAME(ERROR_PROCEDURE(), 1))),
                                     ERROR_LINE()
                                 ),
                    /* concat handles nulls as empty string */
                    CAST(SESSION_CONTEXT(@call_stack_name) AS NVARCHAR(4000))
                );

    /* assign the output variables */
    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorState = ERROR_STATE(),
           @last_schema = PARSENAME(@procName, 2),
           /* find the min severity level that we should issues to match the issue but not fail if we don't have sysadmin */
           @ErrorSeverity = (
				SELECT MIN(val)
				FROM
				(VALUES
					(ERROR_SEVERITY(), 1),
					(18, 0)
				) AS x (val, isSysAdmin)
				WHERE isSysAdmin >= IS_SRVROLEMEMBER('sysadmin')
			);

    /* The min nesting will be the outer proc plus this one */
    IF @@NESTLEVEL = 2
    BEGIN
        SELECT
            /* top level error message */
            @ErrorMessage = FORMATMESSAGE(
                               N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: %s, %sStackTrace: %s',
                               @ErrorNumber,
                               @ErrorSeverity,
                               @ErrorState,
                               @procName,
                               ERROR_LINE(),
                               @ErrorMessage,
                               CHAR(13) + CHAR(10),
                               @call_stack
                           ),
            /* assignment to clear the session context information */
            @call_stack = NULL,
            @ErrorNumber = NULL,
            @last_schema = NULL;
    END;

    /* set/clear the session context values before we goto the prior level
    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-session-context-transact-sql?view=sql-server-ver15  */
    EXEC dbo.sp_set_session_context @key = @proc_schema_name, @value = @last_schema;
    EXEC dbo.sp_set_session_context @key = @call_stack_name, @value = @call_stack;
    EXEC dbo.sp_set_session_context @key = @errornumber_name, @value = @ErrorNumber;

END;
GO

StackTrace Example.sql

USE tempdb;


GO
IF OBJECT_ID(N'dbo.spCatchTest1') IS NOT NULL BEGIN DROP PROCEDURE dbo.spCatchTest1; END;
GO

CREATE PROCEDURE dbo.spCatchTest1
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        EXEC dbo.spCatchTest2;
    END TRY
    BEGIN CATCH
		-- errror catch variables
		DECLARE @ErrorMessage NVARCHAR(4000),
				@ErrorSeverity INT,
				@ErrorState INT =   0,
				@proc_name sysname = CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)), '.', QUOTENAME(OBJECT_NAME(@@PROCID)));

        EXEC dbo.ErrorHandler @procName = @proc_name,
                              @ErrorMessage = @ErrorMessage OUTPUT,
                              @ErrorSeverity = @ErrorSeverity OUTPUT,
                              @ErrorState = @ErrorState OUTPUT;
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
END;
GO


IF OBJECT_ID(N'dbo.spCatchTest2') IS NOT NULL BEGIN DROP PROCEDURE dbo.spCatchTest2; END;
GO

CREATE PROCEDURE dbo.spCatchTest2
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
		-- comment line
        EXEC dbo.spCatchTest3;
    END TRY
    BEGIN CATCH
		-- errror catch variables
		DECLARE @ErrorMessage NVARCHAR(4000),
				@ErrorSeverity INT,
				@ErrorState INT =   0,
				@proc_name sysname = CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)), '.', QUOTENAME(OBJECT_NAME(@@PROCID)));

        EXEC dbo.ErrorHandler @procName = @proc_name,
                              @ErrorMessage = @ErrorMessage OUTPUT,
                              @ErrorSeverity = @ErrorSeverity OUTPUT,
                              @ErrorState = @ErrorState OUTPUT;
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
END;
GO
IF OBJECT_ID(N'dbo.spCatchTest3') IS NOT NULL BEGIN DROP PROCEDURE dbo.spCatchTest3; END;
GO

CREATE PROCEDURE dbo.spCatchTest3
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
		-- comment line 1
		-- comment line 2
		-- comment line 3
        SELECT 1/0
    END TRY
    BEGIN CATCH
		-- errror catch variables
		DECLARE @ErrorMessage NVARCHAR(4000),
				@ErrorSeverity INT,
				@ErrorState INT =   0,
				@proc_name sysname = CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)), '.', QUOTENAME(OBJECT_NAME(@@PROCID)));

        EXEC dbo.ErrorHandler @procName = @proc_name,
                              @ErrorMessage = @ErrorMessage OUTPUT,
                              @ErrorSeverity = @ErrorSeverity OUTPUT,
                              @ErrorState = @ErrorState OUTPUT;
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
END;
GO

EXEC dbo.spCatchTest1