How To Handle Exception In SQL Server

What are Exceptions and Exception Handling?

An exception is an event, which occurs during the execution of a program, and disrupts the normal flow of the program's instructions. If we want the flow of the program to not break when an exception occurs then we have to define some mechanism and this mechanism is called Exception Handling.

Types of Errors in SQL Server 

There are two types of errors in SQL Server.

1. Syntax Error
2. Runtime Error

Syntax Errors

Syntax errors are usually easy to detect because the Code Editor points them out. A syntax error occurs if you try writing code that Transact-SQL does not allow.

• · If you try to place an operator or a keyword incorrectly, the Code Editor would show it to you as an error. For example:

  Here error occurred because of “INT” an operator that is used wrongly. The correct syntax is

  DECLARE @nNUMBER INT = 100;

• If you incorrectly type a keyword or an operator or misspelled it, the Code Editor will point it out. For example:

  An error occurred because of misspelled “DECLARE”.

Run-Time Errors

A runtime error is an application error that occurs during program execution. These errors can be difficult to fix because sometimes they are not clearly identified or are external to the database.

• Trying to use a function, a stored procedure, or a trigger that is not available in the database.

• Trying to perform an operation that either a variable or an object cannot handle.

• Trying to perform a bad calculation such as a division by 0.

Handling an Exception

Exception handling is the ability to deal with errors that occur or can occur on a database at the time of code execution. To handle exceptions, Transact-SQL provides a general formula.

BEGIN TRY    
    -- write the normal code you want to execute    
END TRY    
BEGIN CATCH    
    --handle exceptions here    
END CATCH  

 If you create a try block, you must also create a catch block and there must not be any Transact-SQL code between the END TRY and the BEGIN CATCH lines. For Example:

If we execute the below code then we will get an error as “Conversion failed”. But if we execute the code using TRY CATCH then it will not through any error and run successfully.

DECLARE @nResult INT;    
SET @nResult = 'Amit Mohanty'   

Try to execute using TRY CATCH

Here in the CATCH section, we can handle our exception.

If no error occurs in the try block, then it executes successfully and the execution skips the catch block if an error occurs in the TRY block then we will get the message “Something wrong in your code.”

Identify Errors

To identify an error that has occurred, Transact-SQL provides various functions like

• ERROR_LINE()
• ERROR_NUMBER()
• @@ERROR
• ERROR_MESSAGE()
• ERROR_SEVERITY()
• ERROR_STATE()
• RAISERROR()

ERROR_LINE()

This function is used to know where the error has occurred in the code. It doesn't take any argument. It returns a number that represents the line number where the error occurred. For example:

BEGIN TRY    
    DECLARE @nResult INT;    
    DECLARE @sGrade NVARCHAR(20);    
    
    SET @nResult = 98;    
    SET @sGrade = @nResult + 'A';    
    
    SELECT @nResult AS Result, @sGrade AS Grade;    
END TRY    
BEGIN CATCH    
    PRINT N'Error Line: ' + CAST(ERROR_LINE() AS NVARCHAR(50));    
END CATCH   

By executing the above code we will get at which line the error occurred.

ERROR_NUMBER()

This function returns a specific number, which is just a type of identity of the error.

BEGIN TRY    
    DECLARE @nResult INT;    
    DECLARE @sGrade NVARCHAR(20);    
    
    SET @nResult = 98;    
    SET @sGrade = @nResult + 'A';    
    
    SELECT @nResult AS Result, @sGrade AS Grade;    
END TRY    
BEGIN CATCH    
    PRINT N'Error Number: ' + CAST(ERROR_NUMBER() AS NVARCHAR(50));    
END CATCH   

 After executing the above code we will get the error number.

@@ERROR

In the older version of Microsoft SQL Server to get the error number, we used this function. You can still use this function to find oot the error number. This function is the same as the ERROR_NUMBER().

BEGIN TRY    
    DECLARE @nResult INT;    
    DECLARE @sGrade NVARCHAR(20);    
    
    SET @nResult = 98;    
    SET @sGrade = @nResult + 'A';    
    
    SELECT @nResult AS Result, @sGrade AS Grade;    
END TRY    
BEGIN CATCH    
    PRINT N'Error: ' + CAST(@@ERROR AS NVARCHAR(50));    
END CATCH   

Here also, we will get the error number.

ERROR_MESSAGE()

This function gives us a message related to the error.

BEGIN TRY    
    DECLARE @nResult INT;    
    DECLARE @sGrade NVARCHAR(20);    
    
    SET @nResult = 98;    
    SET @sGrade = @nResult + 'A';    
    
    SELECT @nResult AS Result, @sGrade AS Grade;    
END TRY    
BEGIN CATCH    
    PRINT ERROR_MESSAGE();    
END CATCH   

It will return the message of the error that caused the error.

ERROR_SEVERITY()

This function helps us to identify the severity of an error. It takes no argument and returns an integer value.

BEGIN TRY    
    DECLARE @nResult INT;    
    DECLARE @sGrade NVARCHAR(20);    
    
    SET @nResult = 98;    
    SET @sGrade = @nResult + 'A';    
    
    SELECT @nResult AS Result, @sGrade AS Grade;    
END TRY    
BEGIN CATCH    
    PRINT N'Error Severity: ' + CAST(ERROR_SEVERITY() AS NVARCHAR(50));    
END CATCH   

It will return a numeric value and it is not a level of severity. It is just an indication of the severity.

ERROR_STATE()

The state of an error is a number that specifies the section of code where an error occurred. It takes no argument and returns an integer that specifies the state of the error.

BEGIN TRY    
    DECLARE @nResult INT;    
    DECLARE @sGrade NVARCHAR(20);    
    
    SET @nResult = 98;    
    SET @sGrade = @nResult + 'A';    
    
    SELECT @nResult AS Result, @sGrade AS Grade;    
END TRY    
BEGIN CATCH    
    PRINT N'Error State: ' + CAST(ERROR_STATE() AS NVARCHAR(50));    
END CATCH

   

RAISERROR()

If an error occurs in the code, we can take action, but to better customize how an exception is handled when it occurs, we can raise an error. It takes three required arguments.

• The first argument can be a string-based locally declared variable.
• A second argument is a number that represents the severity level of the error.
• A third argument is a number that represents the error state.

BEGIN TRY    
    DECLARE @nResult INT;    
    DECLARE @sGrade NVARCHAR(20);    
    
    SET @nResult = 98;    
    SET @sGrade = @nResult + 'A';    
    
    SELECT @nResult AS Result, @sGrade AS Grade;    
END TRY    
BEGIN CATCH    
    RAISERROR(N'An Error Is Occur',16,3);    
END CATCH   

There is one more option by which we can also handle exceptions by the THROW keyword. Through our own exception, we can use this.

Syntax

THROW { error_number | @local_variable },{ message | @local_variable},{ state | @local_variable };

Example

THROW 50000, N'There was an error while executing the statement.', 1; 

BEGIN TRY    
    DECLARE @nResult INT;    
    DECLARE @sGrade NVARCHAR(20);    
    
    SET @nResult = 98;    
    SET @sGrade = @nResult + 'A';    
    
    SELECT @nResult AS Result, @sGrade AS Grade;    
END TRY    
BEGIN CATCH    
    THROW 50000, N'There was an error while excecute the statement.', 1;    
END CATCH   

Conclusion

In the above article, we will learn how to handle exceptions in SQL Server. I hope this will help the readers. Happy Coding !!!