RAISERROR - not getting proper error message when calling nested procedures

  • I have the following scenario:

    I have a wrapper procedure that calls another procedure. In case of validation errors within the nested procedure (these are custom validations) we call an error logging procedure and raise a user defined error from within this error logging procedure (with the appropriate error message).

    This error message and the user defined error number needs to bubble up to the outermost procedure and we're facing issues here and we're not able to get both the proper error message and the proper error number to be shown together.

    The reason we need both is there is already existing front end code that displays the error message after taking action on the custom error numbers.

    I'm attaching sample code (with some comments that will hopefully explain the issue better) - is there any way to get the use defined error message and the error number in the outermost procedure?

    -- create custom message id - using 60000 and 60001

    IF EXISTS (SELECT 1 FROM sys.messages WHERE message_id = 60000)

    BEGIN

    EXEC sp_dropmessage @msgnum = 60000

    END

    IF EXISTS (SELECT 1 FROM sys.messages WHERE message_id = 60001)

    BEGIN

    EXEC sp_dropmessage @msgnum = 60001

    END

    GO

    EXEC sp_addmessage @msgnum = 60000, @severity = 16,

    @msgtext = N'Even number detected. The number entered is %d';

    GO

    EXEC sp_addmessage @msgnum = 60001, @severity = 16,

    @msgtext = N'Odd number detected. The number entered is %d';

    GO

    -- drop any existing procs with same name first

    -- named to hopefully not conflict with any existing procs

    IF OBJECT_ID('dbo.xxx_OutermostProcedure') IS NOT NULL

    BEGIN

    DROP PROCEDURE dbo.xxx_OutermostProcedure

    END

    IF OBJECT_ID('dbo.xxx_InnerProcedure') IS NOT NULL

    BEGIN

    DROP PROCEDURE dbo.xxx_InnerProcedure

    END

    IF OBJECT_ID('dbo.xxx_ErrorLogProcedure') IS NOT NULL

    BEGIN

    DROP PROCEDURE dbo.xxx_ErrorLogProcedure

    END

    GO

    CREATE PROCEDURE dbo.xxx_OutermostProcedure

    @Param int

    AS

    -- this is the outermost procedure

    -- this calls the inner procedure

    -- and the exception message should bubble up to this level

    BEGIN

    DECLARE @ErrNum int, @ErrMsg nvarchar(4000),@ErrSev int, @Errline int,@ErrState int

    DECLARE @Err int

    BEGIN TRY

    EXEC dbo.xxx_InnerProcedure @Param

    END TRY

    BEGIN CATCH

    SELECT

    @ErrNum = ERROR_NUMBER(),

    @ErrMsg = ERROR_MESSAGE(),

    @ErrSev = ERROR_SEVERITY(),

    @ErrState = ERROR_STATE(),

    @Errline = ERROR_LINE()

    END CATCH

    -- If I use @ErrMsg then the message comes out properly

    -- however the error number is now 50000 and this needs to be the

    -- custom created error number which is 60000 or 60001

    -- uncomment the line below and the message shows up fine

    -- but the error number is now 50000

    --RAISERROR(@ErrMsg,@ErrSev,@ErrState)

    RAISERROR(@ErrNum,@ErrSev,@ErrState)

    END

    GO

    CREATE PROCEDURE dbo.xxx_InnerProcedure

    @Param int

    AS

    BEGIN

    SELECT @Param

    -- proc does something and logs in case of validation failures

    -- in this case if @Param is divisble by 2 it should raise an error

    -- with error message 60000 content followed by the number passed in

    IF @Param%2 = 0

    BEGIN

    EXEC dbo.xxx_ErrorLogProcedure @Param

    END

    -- in this case if @Param is divisble by 3 it should raise an error

    -- with error message 60001 content followed by the number passed in

    IF @Param%3 = 0

    BEGIN

    EXEC dbo.xxx_ErrorLogProcedure @Param

    END

    END

    GO

    CREATE PROCEDURE dbo.xxx_ErrorLogProcedure

    @Param int

    AS

    BEGIN

    -- perform logging action and then raise the error

    IF @Param%2=0

    BEGIN

    RAISERROR(60000,16,1,@Param)

    END

    IF @Param%3=0

    BEGIN

    RAISERROR(60001,16,1,@Param)

    END

    END

    GO

    -- this should give message saying "Msg 60000, Level 16, State 1" followed by

    -- "The number entered is 10"

    -- however it gives "The number entered is (null)"

    EXEC dbo.xxx_OutermostProcedure 10

    GO

    -- this should give message saying "Msg 60001, Level 16, State 1" followed by

    -- "The number entered is 15"

    -- however it gives "The number entered is (null)"

    EXEC dbo.xxx_OutermostProcedure 15

    GO

    -- this raises the error correctly

    -- gives ""The number entered is 10" and msg 60000

    EXEC dbo.xxx_InnerProcedure 10

    GO

    -- this raises the error correctly

    -- gives ""The number entered is 15" and msg 60001

    EXEC dbo.xxx_InnerProcedure 15

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply