Rethrowing a T-SQL Error

Question

In the following routine that I found here:

ALTER PROCEDURE [dbo].[usp_RethrowError]
AS -- Return if there is no error information to retrieve.
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE @ErrorMessage           NVARCHAR(4000),
            @OriginalErrorNumber    INT,
            @RethrownErrorNumber    INT,
            @ErrorSeverity          INT,
            @ErrorState             INT,
            @ErrorLine              INT,
            @ErrorProcedure         NVARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT
         @OriginalErrorNumber    = ERROR_NUMBER()
        ,@ErrorSeverity          = ERROR_SEVERITY()
        ,@ErrorSeverity          = ERROR_SEVERITY()
        ,@ErrorState             = ERROR_STATE()
        ,@ErrorLine              = ERROR_LINE()
        ,@ErrorProcedure         = ISNULL(ERROR_PROCEDURE(),'-');

    --Severity levels from 0 through 18 can be specified by any user. 
    --Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions
    IF @OriginalErrorNumber < 19 
        SET @RethrownErrorNumber = @OriginalErrorNumber
    ELSE
        SET @RethrownErrorNumber = 18

    -- Building the message string that will contain original
    -- error information.
    SELECT
        @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: ' + ERROR_MESSAGE();


    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR (@ErrorMessage,
               @ErrorSeverity,
               @ErrorState,
               @RethrownErrorNumber,        -- parameter: original error number or 18, if the original was >=19.
               @ErrorSeverity,              -- parameter: original error severity.
               @ErrorState,                 -- parameter: original error state.
               @ErrorProcedure,             -- parameter: original error procedure name.
               @ErrorLine                   -- parameter: original error line number.
              );

Can someone explain the following line:

 SELECT
        @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: ' + ERROR_MESSAGE();

I realize that the occurences of % are placeholders for a signed integers (%d) and a strinf (%s), but I don't understand which variables are mapped to these placeholders. They do not seem to map to the parameters specified in the RAISERROR call:

RAISERROR (@ErrorMessage,
           @ErrorSeverity,
           @ErrorState,
           @RethrownErrorNumber,        -- parameter: original error number or 18, if the original was >=19.
           @ErrorSeverity,              -- parameter: original error severity.

       @ErrorState,                 -- parameter: original error state.
       @ErrorProcedure,             -- parameter: original error procedure name.
       @ErrorLine                   -- parameter: original error line number.
          );

I made two small changes to the subroutine, one to lower the severity if > 19 and the other to use the original State rather than always passing 1.

If you guys don't shoot this routine down with my minor changes down too badly, I will add logging teh error info to a user table just prior to rethrowing.

To call:

DECLARE @Zero INT
SET @Zero = 0

BEGIN TRY
 SELECT 5 / @Zero
END TRY
BEGIN CATCH
 PRINT 'We have an error...'
 EXEC usp_RethrowError
END CATCH

Follow up questions:

1) The link above mentions that this routine would not work for deadlocks. Any reason why?

2) I added the "IF @OriginalErrorNumber < 19" part. I am not too concerned that if an error >18 occurs that the error will be rethrown will a severity of 18. In any event, I plab to abort and the original severity will be logged. Is there anything else in this routine that I need to be worried about?

Side note: it is possible that ERROR_MESSAGE() would have some printf()-style substitution strings in it such as %s. I would recommend passing in ERROR_MESSAGE() using + 'Message: %s' and by including ERROR_MESSAGE() at the end of your new RAISERROR() call’s argument list, because otherwise any printf()-style substitution chars in ERROR_MESSAGE() will be replaced by TSQL with the string (null) when the substitution chars should be passed through without substitution. — binki
– binki, Commented Nov 11, 2013 at 15:46

3martini · Accepted Answer · 2011-11-08 02:47:49Z

3

Hope this will help!

You are on the right track, ErrorMessage is the pattern string that is consumed by RAISERROR. Looking at the grammar structure of RAISERROR will clear up the confusion:

RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]

The first three required arguments are the message pattern string (msg_str), severity, and state. These are followed by the optional arguments which will replace the substitution parameters in the msg_str.

So the code lets:

msg_str be ErrorMessage

severity be ErrorSeverity

state be ErrorState

arguments be RethrownErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine

Reference http://msdn.microsoft.com/en-us/library/ms178592.aspx

answered Nov 8, 2011 at 2:47

3martini

5102 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chad Over a year ago

I think that the % placeholders line up with the arguments you specified, beginning with the 4th parameter,@RethrownErrorNumber, that I pass to the RAISERROR routine. The first 3 parameters must serve just to rethrow the error. The placeholders refers respectively to the argument params (4th param onward.) Thank you!

Collectives™ on Stack Overflow

Rethrowing a T-SQL Error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related