-- 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