Mechanism for Error Handling in MS SQL Server Transact-SQL

Here, I am going to cover the basics of Try-Catch error handling mechanism in T-SQL which is introduced in SQL Server 2005. It includes the usage of error functions to return information about the error using the Try-Catch block in the queries. There are also other types of SQL errors.

Capture errors in SQL Server with simple syntax in 2 steps across 3 scenarios.

SQL Server uses the following basic syntax to capture errors in Transact-SQL statements in just two steps in three different working scenarios:

WORKING WITH TRY-CATCH BLOCK AND ERROR FUNCTIONS

1 STEP 1:
2 BEGIN TRY
3       --Write necessary queries without transactions 
4 END TRY
5 
6 STEP 2:
7 BEGIN CATCH
8 SELECT
9 DECLARE @ErrorNumber INT = ERROR_NUMBER();
10 DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
11 DECLARE @ErrorState INT = ERROR_STATE();
12 DECLARE @ErrorProcedure NVARCHAR(MAX) = ERROR_PROCEDURE();
13 DECLARE @ErrorLine INT = ERROR_LINE();
14 DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();
15 
16 PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
17 PRINT 'Actual error severity: ' + CAST(@ErrorSeverity AS VARCHAR(10));
18 PRINT 'Actual error state: ' + CAST(@ErrorState AS VARCHAR(10));
19 PRINT 'Actual error procedure: ' + @ErrorProcedure;
20 PRINT 'Actual error line: ' + CAST(@ErrorLine AS VARCHAR(10));
21 PRINT 'Actual error message: ' + @ErrorMessage;
22 END CATCHCode language: PHP (php)

In the scope of the Catch block the error functions return error-related information that you can reference in your T-SQL statements. Currently, SQL Server supports the following functions for this purpose:

• ERROR_NUMBER(): The number assigned to the error
• ERROR_LINE(): The line number inside the routine that caused the error
• ERROR_MESSAGE(): The error message text
• ERROR_SEVERITY(): The error severity
• ERROR_STATE(): The error state number
• ERROR_PROCEDURE(): The name of the stored procedure or trigger that generated the error

WORKING WITH TRY-CATCH BLOCK, ERROR FUNCTIONS AND @@TRANCOUNT BUILT-IN FUNCTION

@@TRANCOUNT function is used to determine whether any transactions are still open. If there is a situation where the numbers of the opened transactions are at least one (in case of an error occurs) then roll back transaction can take place. 

1  STEP 1:
2  BEGIN TRY
3  --Write necessary queries with transactions
4  END TRY
5 
6  STEP 2:
7  BEGIN CATCH
8  IF @@TRANCOUNT > 0
9  ROLLBACK TRANSACTION
10 
11 SELECT
12 DECLARE @ErrorNumber INT = ERROR_NUMBER();
13 DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
14 DECLARE @ErrorState INT = ERROR_STATE();
15 DECLARE @ErrorProcedure NVARCHAR(MAX) = ERROR_PROCEDURE();
16 DECLARE @ErrorLine INT = ERROR_LINE();
17 DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();
18 
19 PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
20 PRINT 'Actual error severity: ' + CAST(@ErrorSeverity AS VARCHAR(10));
21 PRINT 'Actual error state: ' + CAST(@ErrorState AS VARCHAR(10));
22 PRINT 'Actual error procedure: ' + @ErrorProcedure;
23 PRINT 'Actual error line: ' + CAST(@ErrorLine AS VARCHAR(10));
24 PRINT 'Actual error message: ' + @ErrorMessage;
25 END CATCHCode language: PHP (php)

WORKING WITH TRY-CATCH BLOCK AND THROW STATEMENT INSTEAD OF ERROR FUNCTIONS

To simplify returning errors in a Catch block, SQL Server 2012 introduced the Throw statement. With the Throw statement, we don’t have to specify any parameters and the results are more accurate. You should simply include the statement as it is shown in the Catch block.

1  STEP 1:
2  BEGIN TRY
3       --Write necessary queries with/without transactions
4  END TRY
5 
6  STEP 2:
7  BEGIN CATCH
8  IF @@TRANCOUNT > 0
9  ROLLBACK TRANSACTION
10 
11 THROW
12 END CATCHCode language: PHP (php)

To conclude

• In SQL Server, there are two main mechanisms for dealing with unexpected situations: the TRY-CATCH block and the RAISERROR statement.
• The TRY-CATCH block is a more general-purpose mechanism that can be used to deal with any unexpected situation.
• The RAISERROR statement is a more specialized mechanism that is used to raise custom unexpected situations.
• The TRY block contains the code that may cause unexpected situations. If an unexpected situation occurs in the TRY block, the control is transferred to the CATCH block.
• The CATCH block contains the code that deals with the unexpected situation.
• By using the TRY-CATCH block and the RAISERROR statement, you can ensure that your SQL Server code is able to deal with unexpected situations gracefully.

Learn about fixing update statement Errors caused by foreign key constraints in SQL in this blog.