Nested stored procedures and catch blocks - ERROR_PROCEDURE() issue

3 votes
2 answers
1880 views
                          I have noticed something odd, and I am hoping someone in the community has an answer for me. I have noticed that when a stored procedure is nested it **seems** that all the blocks prior to last catch block have a weird bug with the ERROR_PROCEDURE() function where it returns the previous blocks procedure name. **NOTE:** This only occurs when using RAISERROR. When using THROW, the very last procedure in the chain is always reported. 

Since that was probable confusing, here is an example.

	SET NOCOUNT ON

	IF OBJECT_ID (N'tempdb..#spCatchTest1') IS NOT NULL BEGIN
		DROP PROCEDURE #spCatchTest1 
	END
	GO 

	CREATE PROCEDURE #spCatchTest1 
	AS 
	BEGIN
		BEGIN TRY 
			EXEC #spCatchTest2		
		END TRY 
		BEGIN CATCH 
			PRINT 'CATCH EXPECTED=#spCatchTest1; ACTUAL=' + ERROR_PROCEDURE()	
			DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE()
			RAISERROR(@Err, 16, 10);
			--;THROW
		END CATCH;
	END
	GO

	IF OBJECT_ID (N'tempdb..#spCatchTest2') IS NOT NULL BEGIN
		DROP PROCEDURE #spCatchTest2 
	END
	GO 

	CREATE PROCEDURE #spCatchTest2 
	AS 
	BEGIN
		BEGIN TRY 
			EXEC #spCatchTest3		
		END TRY 
		BEGIN CATCH 
			PRINT 'CATCH EXPECTED=#spCatchTest2; ACTUAL=' + ERROR_PROCEDURE()	
			DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE()
			RAISERROR(@Err, 16, 10);
			--;THROW
		END CATCH;
	END
	GO

	IF OBJECT_ID (N'tempdb..#spCatchTest3') IS NOT NULL BEGIN
		DROP PROCEDURE #spCatchTest3 
	END
	GO 

	CREATE PROCEDURE #spCatchTest3 
	AS 
	BEGIN
		BEGIN TRY 
			EXEC #spCatchTest4		
		END TRY 
		BEGIN CATCH 
			PRINT 'CATCH EXPECTED=#spCatchTest3; ACTUAL=' + ERROR_PROCEDURE()	
			DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE()
			RAISERROR(@Err, 16, 10);
			--;THROW
		END CATCH;
	END
	GO

	IF OBJECT_ID (N'tempdb..#spCatchTest4') IS NOT NULL BEGIN
		DROP PROCEDURE #spCatchTest4
	END
	GO 

	CREATE PROCEDURE #spCatchTest4 
	AS 
	BEGIN
		BEGIN TRY 
			SELECT 1/0		
		END TRY 
		BEGIN CATCH 
			PRINT 'CATCH EXPECTED=#spCatchTest4; ACTUAL=' + ERROR_PROCEDURE()	
			DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE()
			RAISERROR(@Err, 16, 10);
			--;THROW
		END CATCH;
	END
	GO

	EXEC #spCatchTest1 

This will output:

    CATCH EXPECTED=#spCatchTest4; ACTUAL=#spCatchTest4
    CATCH EXPECTED=#spCatchTest3; ACTUAL=#spCatchTest4
    CATCH EXPECTED=#spCatchTest2; ACTUAL=#spCatchTest3
    CATCH EXPECTED=#spCatchTest1; ACTUAL=#spCatchTest2
    Msg 50000, Level 16, State 10, Procedure #spCatchTest1, Line 11 [Batch Start Line 81]
    Divide by zero error encountered.

As you can see on the second line, the top level catch incorrectly reported the procedure name. This appears to **only** affect the procs that occur in the chain after the error. 

Has anyone else noticed this? Plus, it is a bug, correct? Is there anyway to get the correct procedure name when nested?

EDIT: To clarify, I am essentially trying to build a way to get the stacktrace from a very nested set of stored procs we are inheriting. Think spaghetti monster. With the native CATCH->THROW you only get the top level stored proc name. Here is an example of what I was trying to do:

	USE tempdb
	GO


	IF OBJECT_ID (N'GetErrorInfo') IS NOT NULL BEGIN
		DROP PROCEDURE GetErrorInfo 
	END
	GO 

	CREATE PROCEDURE GetErrorInfo (
		@ErrorNumber INT, 
		@ErrorMessage nvarchar(4000) OUTPUT,
		@ErrorSeverity INT OUTPUT,
		@ErrorState INT,
		@ErrorProcedure nvarchar(128),
		@ErrorLine INT,
		@NestLevel INT
	) AS 
	BEGIN
		DECLARE @msg NVARCHAR(4000), 
			@StackTrace NVARCHAR(4000),
			@i INT = PATINDEX('%%', @ErrorMessage) -- find the stacktrace seperator if there is on on the message

		-- set the error procedure, and	the error severity, if the caller is not sysadmin, limit the severity	
		SELECT @ErrorProcedure = ISNULL(@ErrorProcedure, 'NA'), 
			@ErrorSeverity = CASE WHEN IS_SRVROLEMEMBER('sysadmin') = 0 THEN
				(SELECT MIN(Num1) FROM (VALUES (@ErrorSeverity), (18)) AS Vals(Num1))
			ELSE 
				@ErrorSeverity
			END
	
		IF @i ' + @ErrorProcedure + '.' + CAST(@ErrorLine AS VARCHAR(20)) + ';'
		END ELSE BEGIN
			-- split the stacktrace seperator and add the new stack onto it
			SELECT @msg = LEFT(@ErrorMessage, @i - 1), 
				@StackTrace = RIGHT(@ErrorMessage, LEN(@ErrorMessage) - @i - 1)
		
			SET @ErrorMessage = @msg + '' + @ErrorProcedure  + '.' + CAST(@ErrorLine AS VARCHAR(20)) + ';' + @StackTrace
		END
		-- we are at the top of the call chain, so lets format the final error message.
		IF @NestLevel = 1 BEGIN
			SET @ErrorMessage = FORMATMESSAGE(
				N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: ' + REPLACE(@ErrorMessage, '', CHAR(13) + CHAR(10)),
				@ErrorNumber, 1, @ErrorState, @ErrorProcedure, @ErrorLine) ; 
		END
	END
	GO

	/*
	DECLARE @ErrMessage NVARCHAR(4000) = 'Divide by zero', @ErrSeverity INT = 16, @ErrProcedure NVARCHAR(128) = 'spCatchTest3';
	EXEC GetErrorInfo @ErrorMessage = @ErrMessage OUTPUT, @ErrorSeverity = @ErrSeverity OUTPUT, @ErrorProcedure = @ErrProcedure OUTPUT, @ErrorLine = 32, @NestLevel = 1                             
	SELECT @ErrMessage, @ErrSeverity,  @ErrProcedure

	SELECT @ErrMessage = 'Divide by zerospCatchTest3.32;', @ErrSeverity = 16, @ErrProcedure = 'spCatchTest2';
	EXEC GetErrorInfo @ErrorMessage = @ErrMessage OUTPUT, @ErrorSeverity = @ErrSeverity OUTPUT, @ErrorProcedure = @ErrProcedure OUTPUT, @ErrorLine = 34, @NestLevel = 2                             
	SELECT @ErrMessage, @ErrSeverity,  @ErrProcedure
	*/

	GO
	IF OBJECT_ID (N'spCatchTest1') IS NOT NULL BEGIN
		DROP PROCEDURE spCatchTest1 
	END
	GO 

	CREATE PROCEDURE spCatchTest1 
	AS 
	BEGIN
		DECLARE @trancount int = @@trancount 
		IF @trancount = 0 BEGIN BEGIN TRANSACTION; END --only start a transaction if we are not already under another transaction 

		BEGIN TRY
			EXEC spCatchTest2		
	 
			IF @trancount = 0 BEGIN COMMIT TRAN; END 
		END TRY 
		BEGIN CATCH 
			IF @trancount = 0 BEGIN ROLLBACK TRAN; END 
			DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), 
				@ErrorNumber int = ERROR_NUMBER(), 
				@ErrorState int = ERROR_STATE(),
				@ErrorSeverity INT = ERROR_SEVERITY(), 
				@ErrorProcedure NVARCHAR(128) = OBJECT_NAME(@@PROCID),
				@ErrorLine int = ERROR_LINE();

			EXEC GetErrorInfo @ErrorNumber = @ErrorNumber,
				@ErrorMessage = @ErrorMessage OUTPUT, 
				@ErrorSeverity = @ErrorSeverity OUTPUT, 
				@ErrorState = @ErrorState,
				@ErrorProcedure = @ErrorProcedure, 
				@ErrorLine = @ErrorLine, 
				@NestLevel = @@NESTLEVEL                             
		
			RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
		END CATCH;
	END
	GO


	IF OBJECT_ID (N'spCatchTest2') IS NOT NULL BEGIN
		DROP PROCEDURE spCatchTest2 
	END
	GO 

	CREATE PROCEDURE spCatchTest2 
	AS 
	BEGIN
		DECLARE @trancount int = @@trancount 
		IF @trancount = 0 BEGIN BEGIN TRANSACTION; END --only start a transaction if we are not already under another transaction 

		BEGIN TRY 

		
			EXEC spCatchTest3		
		

	 
			IF @trancount = 0 BEGIN COMMIT TRAN; END 
		END TRY 
		BEGIN CATCH 
			IF @trancount = 0 BEGIN ROLLBACK TRAN; END 
			DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), 
				@ErrorNumber int = ERROR_NUMBER(), 
				@ErrorState int = ERROR_STATE(),
				@ErrorSeverity INT = ERROR_SEVERITY(), 
				@ErrorProcedure NVARCHAR(128) = OBJECT_NAME(@@PROCID),
				@ErrorLine int = ERROR_LINE();

			EXEC GetErrorInfo @ErrorNumber = @ErrorNumber,
				@ErrorMessage = @ErrorMessage OUTPUT, 
				@ErrorSeverity = @ErrorSeverity OUTPUT, 
				@ErrorState = @ErrorState,
				@ErrorProcedure = @ErrorProcedure, 
				@ErrorLine = @ErrorLine, 
				@NestLevel = @@NESTLEVEL                             

			RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
		END CATCH;
	END
	GO

	IF OBJECT_ID (N'spCatchTest3') IS NOT NULL BEGIN
		DROP PROCEDURE spCatchTest3 
	END
	GO 

	CREATE PROCEDURE spCatchTest3 
	AS 
	BEGIN
		DECLARE @trancount int = @@trancount 
		IF @trancount = 0 BEGIN BEGIN TRANSACTION; END --only start a transaction if we are not already under another transaction 

		BEGIN TRY 

			EXEC spCatchTest4
	 
			IF @trancount = 0 BEGIN COMMIT TRAN; END 
		END TRY 
		BEGIN CATCH 
			IF @trancount = 0 BEGIN ROLLBACK TRAN; END 
			DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), 
				@ErrorNumber int = ERROR_NUMBER(), 
				@ErrorState int = ERROR_STATE(),
				@ErrorSeverity INT = ERROR_SEVERITY(), 
				@ErrorProcedure NVARCHAR(128) = OBJECT_NAME(@@PROCID),
				@ErrorLine int = ERROR_LINE();

			EXEC GetErrorInfo @ErrorNumber = @ErrorNumber,
				@ErrorMessage = @ErrorMessage OUTPUT, 
				@ErrorSeverity = @ErrorSeverity OUTPUT, 
				@ErrorState = @ErrorState,
				@ErrorProcedure = @ErrorProcedure, 
				@ErrorLine = @ErrorLine, 
				@NestLevel = @@NESTLEVEL                             

			RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) 
		END CATCH;
	END
	GO

	IF OBJECT_ID (N'spCatchTest4') IS NOT NULL BEGIN
		DROP PROCEDURE spCatchTest4 
	END
	GO 

	CREATE PROCEDURE spCatchTest4 
	AS 
	BEGIN
		DECLARE @trancount int = @@trancount 
		IF @trancount = 0 BEGIN BEGIN TRANSACTION; END --only start a transaction if we are not already under another transaction 

		BEGIN TRY 

			SELECT 1/0
	 
			IF @trancount = 0 BEGIN COMMIT TRAN; END 
		END TRY 
		BEGIN CATCH 
			IF @trancount = 0 BEGIN ROLLBACK TRAN; END 
			DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), 
				@ErrorNumber int = ERROR_NUMBER(), 
				@ErrorState int = ERROR_STATE(),
				@ErrorSeverity INT = ERROR_SEVERITY(), 
				@ErrorProcedure NVARCHAR(128) = OBJECT_NAME(@@PROCID),
				@ErrorLine int = ERROR_LINE();

			EXEC GetErrorInfo @ErrorNumber = @ErrorNumber,
				@ErrorMessage = @ErrorMessage OUTPUT, 
				@ErrorSeverity = @ErrorSeverity OUTPUT, 
				@ErrorState = @ErrorState,
				@ErrorProcedure = @ErrorProcedure, 
				@ErrorLine = @ErrorLine, 
				@NestLevel = @@NESTLEVEL                             

			RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) 
		END CATCH;
	END
	GO

	EXEC dbo.spCatchTest1









                        
Asked by SpaceGhost440 (334 rep)
Mar 30, 2019, 02:47 AM
Last activity: Apr 1, 2019, 02:28 PM
Nested stored procedures and catch blocks - ERROR_PROCEDURE() issue

Related Questions
