SQL ERROR SEVERITY

SQL ERROR SEVERITY is one of the SQL System Function used to return the severity of the error (if occurred). This ERROR SEVERITY 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

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

First, we declared two SQL Server 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));