3

I have noticed something odd, and I am hoping someone in the community has an answer for me. I have noticed that when a stored procedure is nested it seems that all the blocks prior to last catch block have a weird bug with the ERROR_PROCEDURE() function where it returns the previous blocks procedure name. NOTE: This only occurs when using RAISERROR. When using THROW, the very last procedure in the chain is always reported.

Since that was probable confusing, here is an example.

SET NOCOUNT ON

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

CREATE PROCEDURE #spCatchTest1 
AS 
BEGIN
    BEGIN TRY 
        EXEC #spCatchTest2      
    END TRY 
    BEGIN CATCH 
        PRINT 'CATCH EXPECTED=#spCatchTest1; ACTUAL=' + ERROR_PROCEDURE()   
        DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE()
        RAISERROR(@Err, 16, 10);
        --;THROW
    END CATCH;
END
GO

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

CREATE PROCEDURE #spCatchTest2 
AS 
BEGIN
    BEGIN TRY 
        EXEC #spCatchTest3      
    END TRY 
    BEGIN CATCH 
        PRINT 'CATCH EXPECTED=#spCatchTest2; ACTUAL=' + ERROR_PROCEDURE()   
        DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE()
        RAISERROR(@Err, 16, 10);
        --;THROW
    END CATCH;
END
GO

IF OBJECT_ID (N'tempdb..#spCatchTest3') IS NOT NULL BEGIN
    DROP PROCEDURE #spCatchTest3 
END
GO 

CREATE PROCEDURE #spCatchTest3 
AS 
BEGIN
    BEGIN TRY 
        EXEC #spCatchTest4      
    END TRY 
    BEGIN CATCH 
        PRINT 'CATCH EXPECTED=#spCatchTest3; ACTUAL=' + ERROR_PROCEDURE()   
        DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE()
        RAISERROR(@Err, 16, 10);
        --;THROW
    END CATCH;
END
GO

IF OBJECT_ID (N'tempdb..#spCatchTest4') IS NOT NULL BEGIN
    DROP PROCEDURE #spCatchTest4
END
GO 

CREATE PROCEDURE #spCatchTest4 
AS 
BEGIN
    BEGIN TRY 
        SELECT 1/0      
    END TRY 
    BEGIN CATCH 
        PRINT 'CATCH EXPECTED=#spCatchTest4; ACTUAL=' + ERROR_PROCEDURE()   
        DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE()
        RAISERROR(@Err, 16, 10);
        --;THROW
    END CATCH;
END
GO

EXEC #spCatchTest1 

This will output:

CATCH EXPECTED=#spCatchTest4; ACTUAL=#spCatchTest4
CATCH EXPECTED=#spCatchTest3; ACTUAL=#spCatchTest4
CATCH EXPECTED=#spCatchTest2; ACTUAL=#spCatchTest3
CATCH EXPECTED=#spCatchTest1; ACTUAL=#spCatchTest2
Msg 50000, Level 16, State 10, Procedure #spCatchTest1, Line 11 [Batch Start Line 81]
Divide by zero error encountered.

As you can see on the second line, the top level catch incorrectly reported the procedure name. This appears to only affect the procs that occur in the chain after the error.

Has anyone else noticed this? Plus, it is a bug, correct? Is there anyway to get the correct procedure name when nested?

EDIT: To clarify, I am essentially trying to build a way to get the stacktrace from a very nested set of stored procs we are inheriting. Think spaghetti monster. With the native CATCH->THROW you only get the top level stored proc name. Here is an example of what I was trying to do:

USE tempdb
GO


IF OBJECT_ID (N'GetErrorInfo') IS NOT NULL BEGIN
    DROP PROCEDURE GetErrorInfo 
END
GO 

CREATE PROCEDURE GetErrorInfo (
    @ErrorNumber INT, 
    @ErrorMessage nvarchar(4000) OUTPUT,
    @ErrorSeverity INT OUTPUT,
    @ErrorState INT,
    @ErrorProcedure nvarchar(128),
    @ErrorLine INT,
    @NestLevel INT
) AS 
BEGIN
    DECLARE @msg NVARCHAR(4000), 
        @StackTrace NVARCHAR(4000),
        @i INT = PATINDEX('%<>%', @ErrorMessage) -- find the stacktrace seperator if there is on on the message

    -- set the error procedure, and the error severity, if the caller is not sysadmin, limit the severity   
    SELECT @ErrorProcedure = ISNULL(@ErrorProcedure, 'NA'), 
        @ErrorSeverity = CASE WHEN IS_SRVROLEMEMBER('sysadmin') = 0 THEN
            (SELECT MIN(Num1) FROM (VALUES (@ErrorSeverity), (18)) AS Vals(Num1))
        ELSE 
            @ErrorSeverity
        END

    IF @i <= 0 BEGIN
        -- we did not find a stack trace seperator, so lets concat the message with one
        SET @ErrorMessage = @ErrorMessage + ' StackTrace:<>' + @ErrorProcedure + '.' + CAST(@ErrorLine AS VARCHAR(20)) + ';'
    END ELSE BEGIN
        -- split the stacktrace seperator and add the new stack onto it
        SELECT @msg = LEFT(@ErrorMessage, @i - 1), 
            @StackTrace = RIGHT(@ErrorMessage, LEN(@ErrorMessage) - @i - 1)

        SET @ErrorMessage = @msg + '<>' + @ErrorProcedure  + '.' + CAST(@ErrorLine AS VARCHAR(20)) + ';' + @StackTrace
    END
    -- we are at the top of the call chain, so lets format the final error message.
    IF @NestLevel = 1 BEGIN
        SET @ErrorMessage = FORMATMESSAGE(
            N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: ' + REPLACE(@ErrorMessage, '<>', CHAR(13) + CHAR(10)),
            @ErrorNumber, 1, @ErrorState, @ErrorProcedure, @ErrorLine) ; 
    END
END
GO

/*
DECLARE @ErrMessage NVARCHAR(4000) = 'Divide by zero', @ErrSeverity INT = 16, @ErrProcedure NVARCHAR(128) = 'spCatchTest3';
EXEC GetErrorInfo @ErrorMessage = @ErrMessage OUTPUT, @ErrorSeverity = @ErrSeverity OUTPUT, @ErrorProcedure = @ErrProcedure OUTPUT, @ErrorLine = 32, @NestLevel = 1                             
SELECT @ErrMessage, @ErrSeverity,  @ErrProcedure

SELECT @ErrMessage = 'Divide by zero<>spCatchTest3.32;', @ErrSeverity = 16, @ErrProcedure = 'spCatchTest2';
EXEC GetErrorInfo @ErrorMessage = @ErrMessage OUTPUT, @ErrorSeverity = @ErrSeverity OUTPUT, @ErrorProcedure = @ErrProcedure OUTPUT, @ErrorLine = 34, @NestLevel = 2                             
SELECT @ErrMessage, @ErrSeverity,  @ErrProcedure
*/

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

CREATE PROCEDURE spCatchTest1 
AS 
BEGIN
    DECLARE @trancount int = @@trancount 
    IF @trancount = 0 BEGIN BEGIN TRANSACTION; END --only start a transaction if we are not already under another transaction 

    BEGIN TRY
        EXEC spCatchTest2       

        IF @trancount = 0 BEGIN COMMIT TRAN; END 
    END TRY 
    BEGIN CATCH 
        IF @trancount = 0 BEGIN ROLLBACK TRAN; END 
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), 
            @ErrorNumber int = ERROR_NUMBER(), 
            @ErrorState int = ERROR_STATE(),
            @ErrorSeverity INT = ERROR_SEVERITY(), 
            @ErrorProcedure NVARCHAR(128) = OBJECT_NAME(@@PROCID),
            @ErrorLine int = ERROR_LINE();

        EXEC GetErrorInfo @ErrorNumber = @ErrorNumber,
            @ErrorMessage = @ErrorMessage OUTPUT, 
            @ErrorSeverity = @ErrorSeverity OUTPUT, 
            @ErrorState = @ErrorState,
            @ErrorProcedure = @ErrorProcedure, 
            @ErrorLine = @ErrorLine, 
            @NestLevel = @@NESTLEVEL                             

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
    END CATCH;
END
GO


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

CREATE PROCEDURE spCatchTest2 
AS 
BEGIN
    DECLARE @trancount int = @@trancount 
    IF @trancount = 0 BEGIN BEGIN TRANSACTION; END --only start a transaction if we are not already under another transaction 

    BEGIN TRY 


        EXEC spCatchTest3       



        IF @trancount = 0 BEGIN COMMIT TRAN; END 
    END TRY 
    BEGIN CATCH 
        IF @trancount = 0 BEGIN ROLLBACK TRAN; END 
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), 
            @ErrorNumber int = ERROR_NUMBER(), 
            @ErrorState int = ERROR_STATE(),
            @ErrorSeverity INT = ERROR_SEVERITY(), 
            @ErrorProcedure NVARCHAR(128) = OBJECT_NAME(@@PROCID),
            @ErrorLine int = ERROR_LINE();

        EXEC GetErrorInfo @ErrorNumber = @ErrorNumber,
            @ErrorMessage = @ErrorMessage OUTPUT, 
            @ErrorSeverity = @ErrorSeverity OUTPUT, 
            @ErrorState = @ErrorState,
            @ErrorProcedure = @ErrorProcedure, 
            @ErrorLine = @ErrorLine, 
            @NestLevel = @@NESTLEVEL                             

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
    END CATCH;
END
GO

IF OBJECT_ID (N'spCatchTest3') IS NOT NULL BEGIN
    DROP PROCEDURE spCatchTest3 
END
GO 

CREATE PROCEDURE spCatchTest3 
AS 
BEGIN
    DECLARE @trancount int = @@trancount 
    IF @trancount = 0 BEGIN BEGIN TRANSACTION; END --only start a transaction if we are not already under another transaction 

    BEGIN TRY 

        EXEC spCatchTest4

        IF @trancount = 0 BEGIN COMMIT TRAN; END 
    END TRY 
    BEGIN CATCH 
        IF @trancount = 0 BEGIN ROLLBACK TRAN; END 
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), 
            @ErrorNumber int = ERROR_NUMBER(), 
            @ErrorState int = ERROR_STATE(),
            @ErrorSeverity INT = ERROR_SEVERITY(), 
            @ErrorProcedure NVARCHAR(128) = OBJECT_NAME(@@PROCID),
            @ErrorLine int = ERROR_LINE();

        EXEC GetErrorInfo @ErrorNumber = @ErrorNumber,
            @ErrorMessage = @ErrorMessage OUTPUT, 
            @ErrorSeverity = @ErrorSeverity OUTPUT, 
            @ErrorState = @ErrorState,
            @ErrorProcedure = @ErrorProcedure, 
            @ErrorLine = @ErrorLine, 
            @NestLevel = @@NESTLEVEL                             

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) 
    END CATCH;
END
GO

IF OBJECT_ID (N'spCatchTest4') IS NOT NULL BEGIN
    DROP PROCEDURE spCatchTest4 
END
GO 

CREATE PROCEDURE spCatchTest4 
AS 
BEGIN
    DECLARE @trancount int = @@trancount 
    IF @trancount = 0 BEGIN BEGIN TRANSACTION; END --only start a transaction if we are not already under another transaction 

    BEGIN TRY 

        SELECT 1/0

        IF @trancount = 0 BEGIN COMMIT TRAN; END 
    END TRY 
    BEGIN CATCH 
        IF @trancount = 0 BEGIN ROLLBACK TRAN; END 
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), 
            @ErrorNumber int = ERROR_NUMBER(), 
            @ErrorState int = ERROR_STATE(),
            @ErrorSeverity INT = ERROR_SEVERITY(), 
            @ErrorProcedure NVARCHAR(128) = OBJECT_NAME(@@PROCID),
            @ErrorLine int = ERROR_LINE();

        EXEC GetErrorInfo @ErrorNumber = @ErrorNumber,
            @ErrorMessage = @ErrorMessage OUTPUT, 
            @ErrorSeverity = @ErrorSeverity OUTPUT, 
            @ErrorState = @ErrorState,
            @ErrorProcedure = @ErrorProcedure, 
            @ErrorLine = @ErrorLine, 
            @NestLevel = @@NESTLEVEL                             

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) 
    END CATCH;
END
GO

EXEC dbo.spCatchTest1
1
  • Not sure if this is the correct answer, but turns out using OBJECT_NAME(@@PROCID) gives me exactly what I need. As long as the procs aren't temporary. :D Mar 30, 2019 at 3:18

2 Answers 2

3

Plus, it is a bug, correct?

A documentation bug, perhaps. I'm submitting a doc PR to clean up some misleading language.

ERROR_PROCEDURE "returns the name of the stored procedure or trigger where an error occurs"

This means the name of the stored procedure where the error originated, not the one containing the CATCH block. You often only have CATCH blocks at the outermost stored procedure. You might have a deep call stack, and this procedure identifies the procedure that was the source of the error.

1

It doesn't seem bug of ERROR_PPROCEDURE() function. Instead, it's (you can say) limitation of ERROR_MESSAGE() & ERROR_PROCEDURE() function. Error at any level of nesting is an error in current object/SP for these function. And hardening of the error message to RAISEERROR and THROW will always error out the defined message.

If you use

    ;THROW 60000, @Err, 1;

It will give you a similar result as

    RAISERROR(@Err, 16, 10);

But when you use

    ;THROW

It passes out the error occurred with the last level of nesting to the next level till the top, and we can see the final error message.

Thanks!

1
  • The problem is that I was trying to build a way to get the stacktrace of stored procedures. Unfortunately we are inheriting a system where we have a very nested bunch of stored procedures. With the normal THROW or RAISERROR we can only find the top level procedure. Apr 1, 2019 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.