Home > T-SQL > SQL SERVER – Get the Failed Stored Procedure name Using ERROR_PROCEDURE

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

pic12. 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

pic2So, 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

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: