SQL ERROR STATE

SQL ERROR STATE is one of the SQL System Function which returns the state of the error (if occurred). This SQL ERROR STATE function works within the scope of a CATCH block. If you call this function outside the CATCH block, it will return NULL.

For example, we write a series of statements inside the TRY block. And if the server finds an error, then SQL Server ERROR_STATE() inside the CATCH block will be executed, and return an integer value. The basic syntax of the Error_State in SQL Server is as shown below:

ERROR_STATE()

SQL ERROR STATE example

In this example, we are going to show you how to use the Error_State function to find the error severity.

BEGIN TRY  
    SELECT 1/0  
END TRY  
BEGIN CATCH  
    SELECT ERROR_STATE() AS [Error State Result]  
END CATCH  
GO

ERROR STATE Example 2

It is another example to demonstrate the SQL Server Error_State function.

BEGIN TRY
    DECLARE @Number tinyint,
	        @Res tinyint;
    SET @Number = 252;
    SET @Res = @Number + 10;
SELECT @Number AS Number, 
       @Res AS Result;
END TRY
BEGIN CATCH
  PRINT N'Error State = ' + CAST(ERROR_STATE() AS VARCHAR(100));
  PRINT N'Error Message = ' + CAST(ERROR_MESSAGE() AS VARCHAR(100));
END CATCH

In this SQL Server query, we declared two tinyint variables. Next we assigned 252 to @Number variable, and then we added 10 to that variable which will become 262 (Result).

SET @Number = 252; 
SET @Res = @Number + 10;

As we all know that tinyint holds numbers from 0 to 255 means Overflow so, it will exit from the TRY block, and execute the Sql Server error state statements inside our CATCH block. We used the ERROR_MESSAGE function to display the error description

PRINT N'Error State = ' + CAST(ERROR_STATE() AS VARCHAR(100)); 
PRINT N'Error Message = ' + CAST(ERROR_MESSAGE() AS VARCHAR(100));