Nested stored procedures and catch blocks - ERROR_PROCEDURE() issue

Question

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 <= 0 BEGIN
 -- we did not find a stack trace seperator, so lets concat the message with one
 SET @ErrorMessage = @ErrorMessage + ' StackTrace:<>' + @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 zero<>spCatchTest3.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

• SpaceGhost440: Not sure if this is the correct answer, but turns out using OBJECT_NAME(@@PROCID) gives me exactly what I need. As long as the procs aren't temporary. :D

Answer 1

Plus, it is a bug, correct?

A documentation bug, perhaps. I'm submitting a doc PR to clean up some misleading language.

ERROR_PROCEDURE "returns the name of the stored procedure or trigger where an error occurs"

This means the name of the stored procedure where the error originated, not the one containing the CATCH block. You often only have CATCH blocks at the outermost stored procedure. You might have a deep call stack, and this procedure identifies the procedure that was the source of the error.

Answer 2

It doesn't seem bug of ERROR_PPROCEDURE() function. Instead, it's (you can say) limitation of ERROR_MESSAGE() & ERROR_PROCEDURE() function. Error at any level of nesting is an error in current object/SP for these function. And hardening of the error message to RAISEERROR and THROW will always error out the defined message.

If you use

 ;THROW 60000, @Err, 1;

It will give you a similar result as

 RAISERROR(@Err, 16, 10);

But when you use

 ;THROW

It passes out the error occurred with the last level of nesting to the next level till the top, and we can see the final error message.

Thanks!

• SpaceGhost440: The problem is that I was trying to build a way to get the stacktrace of stored procedures. Unfortunately we are inheriting a system where we have a very nested bunch of stored procedures. With the normal THROW or RAISERROR we can only find the top level procedure.