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.
- Syntax Error
- 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 !!!
Join the conversation! Your thoughts help the community grow.