Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

RAISERROR - not getting proper error message when calling nested procedures Expand / Collapse
Author
Message
Posted Monday, August 17, 2009 9:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
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





Post #771991
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse