InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 11:44:55 GMT20http://www.sqlservercentral.com/Forums/Topic771991-149-1.aspxI 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?[code]-- create custom message id - using 60000 and 60001IF EXISTS (SELECT 1 FROM sys.messages WHERE message_id = 60000)BEGIN EXEC sp_dropmessage @msgnum = 60000ENDIF EXISTS (SELECT 1 FROM sys.messages WHERE message_id = 60001)BEGIN EXEC sp_dropmessage @msgnum = 60001ENDGOEXEC sp_addmessage @msgnum = 60000, @severity = 16, @msgtext = N'Even number detected. The number entered is %d';GOEXEC 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 procsIF OBJECT_ID('dbo.xxx_OutermostProcedure') IS NOT NULLBEGIN DROP PROCEDURE dbo.xxx_OutermostProcedureENDIF OBJECT_ID('dbo.xxx_InnerProcedure') IS NOT NULLBEGIN DROP PROCEDURE dbo.xxx_InnerProcedureENDIF OBJECT_ID('dbo.xxx_ErrorLogProcedure') IS NOT NULLBEGIN DROP PROCEDURE dbo.xxx_ErrorLogProcedureENDGOCREATE PROCEDURE dbo.xxx_OutermostProcedure @Param intAS-- this is the outermost procedure -- this calls the inner procedure-- and the exception message should bubble up to this levelBEGIN 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) ENDGOCREATE PROCEDURE dbo.xxx_InnerProcedure@Param intASBEGIN 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 ENDGOCREATE PROCEDURE dbo.xxx_ErrorLogProcedure@Param intASBEGIN -- 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) ENDENDGO-- 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 10GO-- 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 15GO-- this raises the error correctly-- gives ""The number entered is 10" and msg 60000EXEC dbo.xxx_InnerProcedure 10GO-- this raises the error correctly-- gives ""The number entered is 15" and msg 60001EXEC dbo.xxx_InnerProcedure 15[/code]Mon, 17 Aug 2009 09:13:29 GMTwinash