Nested stored procedures and catch blocks - ERROR_PROCEDURE() issue

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

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.

StackExchange Database Administrators Q#233551, answer score: 3