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

 
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:
How To Handle Exception In SQL Server
 
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:
How To Handle Exception In SQL Server
 
Error occurred because of misspelled of “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.
How To Handle Exception In SQL Server
  • Trying to perform an operation that either a variable or an object cannot handle.
How To Handle Exception In SQL Server
  • Trying to performing a bad calculation such as a division by 0.
How To Handle Exception In SQL Server
 

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.
  1. BEGIN TRY    
  2.     -- write the normal code you want to execute    
  3. END TRY    
  4. BEGIN CATCH    
  5.     --handle exceptions here    
  6. 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.
  1. DECLARE @nResult INT;    
  2. SET @nResult = 'Amit Mohanty'   
How To Handle Exception In SQL Server
 
Try to execute using TRY CATCH
 
How To Handle Exception In SQL Server
 
Here in the CATCH section, we can handle our exception.
 
How To Handle Exception In SQL Server
 
If no error occurs in the try block, then it executes successfully and the execution skips the catch block and if an error occurs in TRY block then we will get the message as “Something wrong in your code.”
 

Identify Errors

 
To identifying 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:
  1. BEGIN TRY    
  2.     DECLARE @nResult INT;    
  3.     DECLARE @sGrade NVARCHAR(20);    
  4.     
  5.     SET @nResult = 98;    
  6.     SET @sGrade = @nResult + 'A';    
  7.     
  8.     SELECT @nResult AS Result, @sGrade AS Grade;    
  9. END TRY    
  10. BEGIN CATCH    
  11.     PRINT N'Error Line: ' + CAST(ERROR_LINE() AS NVARCHAR(50));    
  12. END CATCH   
By executing the above code we will get at which line the error occurred.
 
How To Handle Exception In SQL Server
 
ERROR_NUMBER()
 
This function returns a specific number, which is just a type of identity of the error.
  1. BEGIN TRY    
  2.     DECLARE @nResult INT;    
  3.     DECLARE @sGrade NVARCHAR(20);    
  4.     
  5.     SET @nResult = 98;    
  6.     SET @sGrade = @nResult + 'A';    
  7.     
  8.     SELECT @nResult AS Result, @sGrade AS Grade;    
  9. END TRY    
  10. BEGIN CATCH    
  11.     PRINT N'Error Number: ' + CAST(ERROR_NUMBER() AS NVARCHAR(50));    
  12. END CATCH   
 After execute the above code we will get the error number.
 
How To Handle Exception In SQL Server
 
@@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().
  1. BEGIN TRY    
  2.     DECLARE @nResult INT;    
  3.     DECLARE @sGrade NVARCHAR(20);    
  4.     
  5.     SET @nResult = 98;    
  6.     SET @sGrade = @nResult + 'A';    
  7.     
  8.     SELECT @nResult AS Result, @sGrade AS Grade;    
  9. END TRY    
  10. BEGIN CATCH    
  11.     PRINT N'Error: ' + CAST(@@ERROR AS NVARCHAR(50));    
  12. END CATCH   
 Here also, we will get the error number.
 
How To Handle Exception In SQL Server
 
ERROR_MESSAGE()
 
This function gives us a message related to the error.
  1. BEGIN TRY    
  2.     DECLARE @nResult INT;    
  3.     DECLARE @sGrade NVARCHAR(20);    
  4.     
  5.     SET @nResult = 98;    
  6.     SET @sGrade = @nResult + 'A';    
  7.     
  8.     SELECT @nResult AS Result, @sGrade AS Grade;    
  9. END TRY    
  10. BEGIN CATCH    
  11.     PRINT ERROR_MESSAGE();    
  12. END CATCH   
 It will return the message of the error that caused the error.
 
How To Handle Exception In SQL Server
 
ERROR_SEVERITY()
 
This function helps us to identify the severity of an error. It takes no argument and returns an integer value.
  1. BEGIN TRY    
  2.     DECLARE @nResult INT;    
  3.     DECLARE @sGrade NVARCHAR(20);    
  4.     
  5.     SET @nResult = 98;    
  6.     SET @sGrade = @nResult + 'A';    
  7.     
  8.     SELECT @nResult AS Result, @sGrade AS Grade;    
  9. END TRY    
  10. BEGIN CATCH    
  11.     PRINT N'Error Severity: ' + CAST(ERROR_SEVERITY() AS NVARCHAR(50));    
  12. END CATCH   
 It will return a numeric value and it is not a level of severity. It is just an indication of the severity.
 
How To Handle Exception In SQL Server
 
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.
  1. BEGIN TRY    
  2.     DECLARE @nResult INT;    
  3.     DECLARE @sGrade NVARCHAR(20);    
  4.     
  5.     SET @nResult = 98;    
  6.     SET @sGrade = @nResult + 'A';    
  7.     
  8.     SELECT @nResult AS Result, @sGrade AS Grade;    
  9. END TRY    
  10. BEGIN CATCH    
  11.     PRINT N'Error State: ' + CAST(ERROR_STATE() AS NVARCHAR(50));    
  12. END CATCH   
How To Handle Exception In SQL Server
 
RAISERROR()
 
If an error occurs in 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.
 
The second argument is a number that represents the severity level of the error.
 
The third argument is a number that represents the error state.
  1. BEGIN TRY    
  2.     DECLARE @nResult INT;    
  3.     DECLARE @sGrade NVARCHAR(20);    
  4.     
  5.     SET @nResult = 98;    
  6.     SET @sGrade = @nResult + 'A';    
  7.     
  8.     SELECT @nResult AS Result, @sGrade AS Grade;    
  9. END TRY    
  10. BEGIN CATCH    
  11.     RAISERROR(N'An Error Is Occur',16,3);    
  12. END CATCH   
How To Handle Exception In SQL Server
 
There is one more option by which we can also handle exception by THROW key word. To 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 excecute the statement.', 1; 
  1. BEGIN TRY    
  2.     DECLARE @nResult INT;    
  3.     DECLARE @sGrade NVARCHAR(20);    
  4.     
  5.     SET @nResult = 98;    
  6.     SET @sGrade = @nResult + 'A';    
  7.     
  8.     SELECT @nResult AS Result, @sGrade AS Grade;    
  9. END TRY    
  10. BEGIN CATCH    
  11.     THROW 50000, N'There was an error while excecute the statement.', 1;    
  12. END CATCH   
How To Handle Exception In SQL Server
 
In the above article we will learn how to handle exceptions in SQL Server.
 
I hope this will help the readers. Happy Coding !!!