Home > T-SQL > SQL SERVER – Obtain ERROR_STATE code for troubleshooting

SQL SERVER – Obtain ERROR_STATE code for troubleshooting


ERROR_STATE:

This function returns the error state number of an error occurred inside the try..catch block. This state number is different from @@ERROR number. Error State will differ for a same error but in different situation. So, to pin point the particular cause, ERROR_STATE() is very useful when referring Microsoft KB article for troubleshooting.

it returns an integer value. Error_State must be used within the try…catch block otherwise it will return a null value. If we use nested try…catch block it returns the error_state value for the current scope.

Syntax

ERROR_STATE()

Example

1. A simple example to show how to use of ERROR_STATE() function.

DECLARE @int AS INTEGER
BEGIN TRY
    SET @int = 1/0.0
END TRY

BEGIN CATCH

    PRINT 'Error : ' + CAST(@@ERROR AS VARCHAR(20))
    PRINT 'Error State : ' + CAST(ERROR_STATE() AS VARCHAR(20))
    PRINT 'Error Message : ' + ERROR_MESSAGE()    
END CATCH

Output

pic0

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: