Error Handling in SQL with Try-Catch (with Examples)

Doing Error Handling in SQL is essential to have access to transactional processes. In a Database Management System error is something that can translate to what is happening in the system, debugging and correcting an error requires the knowledge of transactional command lines. Here, in this article, we will look closely at how we can try and catch statements in SQL to fix the errors.

Error Handling in SQL

Using techniques in SQL to catch and test errors is a common practice. We can use blocks of codes consisting of T-SQL statements which are simple to execute and used in many instances. There are times when the compiler may give unusual or unnecessary errors, using these methods we can look at these error messages in a more meaningful way. Sometimes it may not be able to solve an error but will give an idea of what the error is.

The TRY and CATCH block in SQL is used to test when an error occurs and control it. Inside the BEGIN TRY part we observe an error, when an error occurs inside the TRY block it then gets transferred to the CATCH block. Inside the CATCH block, we can know exactly what happened, having access to the error we can work on it. 

Here is the syntax:

BEGIN TRY
DECLARE @Result INT;
SET @Result = 1 / 0; -- This will cause a divide by zero error
END TRY
BEGIN CATCH
PRINT 'An error occurred: ' + ERROR_MESSAGE();
PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS NVARCHAR(10));
PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS NVARCHAR(10));
PRINT 'Error state: ' + CAST(ERROR_STATE() AS NVARCHAR(10));
PRINT 'Error line: ' + CAST(ERROR_LINE() AS NVARCHAR(10));
END CATCH;

Let us understand this through an example:

SQL Query:

BEGIN TRY
DECLARE @dividend INT = 10;
DECLARE @divisor INT = 0;
DECLARE @result INT;

SET @result = @dividend / @divisor;

SELECT 'Result: ' + CAST(@result AS VARCHAR);
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_LINE() AS ErrorLine;
END CATCH;

Output:

In this example, we try to perform a division operation where the divisor is set to 0 so that we can get an error when divided by zero error. The TRY block contains the code that might cause an error, and the CATCH block contains the error-handling code.

When an error occurs in the TRY block, the control is transferred to the CATCH block, where we can access error information using functions like ERROR_NUMBER(), ERROR_MESSAGE(), and others.

Catching the Errors

Using the CATCH statement we can find out various details about the error. As already seen in the above example it has predefined functions such as:

ERROR_MESSAGE() - Using this function we can find out the exact reason behind the error.
ERROR_LINE() - This function calculates the line at which the error occurred.
ERROR_NUMBER() - This function shows the error number.
ERROR_SEVERITY() - Using this function the level or the severity of the error is calculated.
ERROR_STATE() - This function checks the state of the error, whether they are resolved or not. 
ERROR_PROCEDURE() - Using this function we can find out the stage of the procedure where the error occurred. 

Nested Try-Catch in SQL

The Nested TRY...CATCH has multiple layers of dense blocks, these blocks perform various error-handling codes. The code starts its execution from the innermost subquery and works its way out to the outermost query.

Let us understand this through an example.

SQL Query:

BEGIN TRY
BEGIN TRY
DECLARE @dividend INT = 10;
DECLARE @divisor INT = 0;
DECLARE @result INT;
SET @result = @dividend / @divisor;
SELECT 'Result: ' + CAST(@result AS VARCHAR);
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_LINE() AS ErrorLine;
END CATCH;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_LINE() AS ErrorLine;
END CATCH;

Output:

In this example, there is an outer TRY...CATCH block and an inner TRY...CATCH block. When an error occurs in the inner TRY block, the control is transferred to the inner CATCH block whereas when an error occurs in the outer TRY block or the code after the inner TRY...CATCH block, the control is transferred to the outer CATCH block.

Is there a try and except in SQL? Yes in SQL TRY and EXCEPT are constructed to handle errors in T-SQL and allow the block to handle the logic behind the error.

Conclusion

In summary, the Try-Catch in SQL is a transactional command that is used to observe and find the error. In this article, we learned about various methods to optimize the functions to catch and solve the error. Ultimately TRY-CATCH helps to build a robust architecture where we can find and identify the error.