ERROR_PROCEDURE (Transact-SQL)

**APPLIES TO:** ![yes](media/yes.png)SQL Server (starting with 2008) ![yes](media/yes.png)Azure SQL Database ![yes](media/yes.png)Azure SQL Data Warehouse ![yes](media/yes.png)Parallel Data Warehouse

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.

Topic link icon Transact-SQL Syntax Conventions

Syntax

ERROR_PROCEDURE ( )

Return Types

nvarchar(128)

Return Value

When called in a stored procedure CATCH block where an error occurs, ERROR_PROCEDURE returns the name of that stored procedure.

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.

In a nested CATCH block, ERROR_PROCEDURE returns the error number specific to the scope of the CATCH block that referenced that CATCH block. For example, the CATCH block of an outer TRY…CATCH construct could have an inner TRY...CATCH construct. Inside that inner CATCH block, ERROR_PROCEDURE returns the number of the error that invoked the inner CATCH block. If ERROR_PROCEDURE runs in the outer CATCH block, it returns the number of the error that invoked that outer CATCH block.

Examples: [!INCLUDEssSDWfull] and [!INCLUDEssPDW]

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  

-----------

(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  

-----------

(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)