ERROR_PROCEDURE (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
This function returns the name of the stored procedure or trigger where an error occurs, if that error caused the CATCH block of a TRY...CATCH construct to execute.
- SQL Server 2017 thru current version returns
schema_name.stored_procedure_name
- SQL Server 2016 returns
stored_procedure_name
Transact-SQL syntax conventions
Syntax
ERROR_PROCEDURE ( )
Return Types
nvarchar(128)
Return Value
When called in a CATCH block, ERROR_PROCEDURE
returns the name of the stored procedure or trigger in which the error originated.
ERROR_PROCEDURE
returns NULL if the error did not occur within a stored procedure or trigger.
ERROR_PROCEDURE
returns NULL when called outside the scope of a CATCH block.
Remarks
ERROR_PROCEDURE
supports calls anywhere within the scope of a CATCH block.
ERROR_PROCEDURE
returns the name of the stored procedure or trigger where an error occurs, regardless of how many times it runs, or where it runs, within the scope of the CATCH
block. This contrasts with a function like @@ERROR, which only returns an error number in the statement immediately following the one that causes an error.
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
A. Using ERROR_PROCEDURE in a CATCH block
This example shows a stored procedure that generates a divide-by-zero error. ERROR_PROCEDURE
returns the name of the stored procedure where the error occurred.
-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'usp_ExampleProc', 'P' ) IS NOT NULL
DROP PROCEDURE usp_ExampleProc;
GO
-- Create a stored procedure that
-- generates a divide-by-zero error.
CREATE PROCEDURE usp_ExampleProc
AS
SELECT 1/0;
GO
BEGIN TRY
-- Execute the stored procedure inside the TRY block.
EXECUTE usp_ExampleProc;
END TRY
BEGIN CATCH
SELECT ERROR_PROCEDURE() AS ErrorProcedure;
END CATCH;
GO
Here is the result set.
-----------
(0 row(s) affected)
ErrorProcedure
--------------------
usp_ExampleProc
(1 row(s) affected)
B. Using ERROR_PROCEDURE in a CATCH block with other error-handling tools
This example shows a stored procedure that generates a divide-by-zero error. Along with the name of the stored procedure where the error occurred, the stored procedure returns information about the error.
-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'usp_ExampleProc', 'P' ) IS NOT NULL
DROP PROCEDURE usp_ExampleProc;
GO
-- Create a stored procedure that
-- generates a divide-by-zero error.
CREATE PROCEDURE usp_ExampleProc
AS
SELECT 1/0;
GO
BEGIN TRY
-- Execute the stored procedure inside the TRY block.
EXECUTE usp_ExampleProc;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_LINE() AS ErrorLine;
END CATCH;
GO
Here is the result set.
-----------
(0 row(s) affected)
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorMessage ErrorLine
----------- ------------- ----------- ---------------- ---------------------------------- -----------
8134 16 1 usp_ExampleProc Divide by zero error encountered. 6
(1 row(s) affected)
See Also
sys.messages (Transact-SQL)
TRY...CATCH (Transact-SQL)
ERROR_LINE (Transact-SQL)
ERROR_MESSAGE (Transact-SQL)
ERROR_NUMBER (Transact-SQL)
ERROR_SEVERITY (Transact-SQL)
ERROR_STATE (Transact-SQL)
RAISERROR (Transact-SQL)
@@ERROR (Transact-SQL)