SQL SERVER – Get the Failed Stored Procedure name Using ERROR_PROCEDURE

ERROR_PROCEDURE () : This function is used to get the stored procedure name or trigger name that failed during execution. This function must be used with in the try..catch block otherwise it returns NULL value.

Syntax

ERROR_PROCEDURE ( )

It returns the data type nvarchar of size 128 bytes.

Example

1. This is a simple example to show what will happen when we execute this function outside the try…catch scope

select ERROR_PROCEDURE ()

Output

2. In this example, I am going to create a sample stored procedure  and test the function error_procedure()

Creating Procedure

create procedure sp_Test
 as
 begin
    declare @int as int
    set @int = 1/0
 End
 GO

If we execute this procedure using the below given script then it returns an error.

exec sp_Test
GO

But, we need the name of the procedure which has got error during execution. So, I am going to alter the previous stored procedure to add try..catch block.

Altered stored procedure

alter procedure sp_Test
 as
 begin
    declare @int as int
 begin try
    set @int = 1/0
 end try
 begin catch
    print 'Failed Procedure Name : ' + ERROR_PROCEDURE()
 end catch
 End
 GO

Now, execute the stored procedure

exec sp_Test
GO

Output

So, we have learned to capture the stored procedure names that are failed during execution. We can use this function in the application and audit or handle the errors gracefully.

Additional Reading

Try…Catch

@@Error