SQL ERROR SEVERITY

SQL ERROR SEVERITY is one of the SQL System Function used to return the severity of the error (if occurred). This function works within the scope of a CATCH block. If you call this function from 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 ERROR_SEVERITY() inside the CATCH block will be executed, and return an integer value. The basic syntax of the Error_Severity in SQL Server is as shown below:

ERROR_SEVERITY()

SQL ERROR SEVERITY example

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

-- SQL Server ERROR_SEVERITY Example
BEGIN TRY  
    SELECT 1/0  
END TRY  
BEGIN CATCH  
    SELECT ERROR_SEVERITY() AS ErrorResult  
END CATCH  
GO

OUTPUT

SQL ERROR SEVERITY Example 2

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

-- SQL Server ERROR_SEVERITY Example
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 Severity = ' + CAST(ERROR_SEVERITY() AS VARCHAR(100));
  PRINT N'Error Message = ' + CAST(ERROR_MESSAGE() AS VARCHAR(100));
END CATCH

OUTPUT

ANALYSIS

First, we declared two tinyint variables

DECLARE @Number tinyint,
	    @Res tinyint;

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 statements inside our CATCH block. We used the ERROR_MESSAGE function to display the error description

PRINT N'Error Severity = ' + CAST(ERROR_SEVERITY() AS VARCHAR(100)); 
PRINT N'Error Message = ' + CAST(ERROR_MESSAGE() AS VARCHAR(100));