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
Best Answer
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.