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 series of statements inside the TRY block. And if server finds error then ERROR_SEVERITY() inside the CATCH block will be executed, and return an integer value.

SQL ERROR SEVERITY Function Syntax

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 SQL Server Error_Severity function to find the error severity.

SQL CODE

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

This is another example to demonstrate the error severity function..

SQL CODE

-- 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));

Thank You for Visiting Our Blog