How to handle errors using TRY...CATCH block in SQL Server?

Question

Ashutosh Patel · Answer

SQL Server Try….Catch Block

Error handling in SQL Server using TRY...CATCH blocks is a structured way of handling exceptions that occur when executing T-SQL code. Here's how you can make the most of TRY...CATCH

Syntax

BEGIN TRY
   -- T-SQL statements that might cause an error
END TRY
BEGIN CATCH
   -- Error handling code
END CATCH

Example Usage
Consider a scenario where you want to insert a record into a table, but you have to gracefully handle any potential errors.

BEGIN TRY
   BEGIN TRANSACTION; -- Start a transaction (if needed)
   
   -- SQL statements that might cause an error
   INSERT INTO YourTable (Column1, Column2) VALUES (Value1, Value2);
   COMMIT TRANSACTION; -- Commit the transaction
END TRY
BEGIN CATCH
   IF @@TRANCOUNT > 0
       ROLLBACK TRANSACTION; -- Rollback the transaction if there is an active one
   -- Error handling: You can log the error, raise an alert, or perform other actions
   PRINT 'Error Message: ' + ERROR_MESSAGE();
   PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(500));
   PRINT 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(500));
   PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(500));
END CATCH

Explanation

TRY Block- The BEGIN TRY block covers special T-SQL statements that can throw an error.
CATCH Block- If an error occurs in the TRY block, execution jumps to the BEGIN CATCH block.
Error Functions- In the CATCH section, you can use the functions ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), and ERROR_NUMBER() to get information about the error that occurred These functions help in documenting the problem and in research.
Transaction Handling- It is good practice to build potentially risky operations (such as insert, update, delete) into a transaction (BEGIN TRANSACTION, COMMIT, ROLLBACK) to ensure data integrity If errors occur, they are reversible after the transaction in a CATCH internal clause to maintain synchronization.

Logging and Notification

Depending on the needs of your application, you can extend error handling to log errors in a table, send notifications (via email, alerts, etc.), or take other appropriate actions

Nesting of TRY…CATCH blocks

You can nest TRY…CATCH blocks to handle errors at different levels of your code execution. For example, a nested TRY...CATCH may handle a particular error differently from an outer block.

Error Propagation- If an error is not caught in the TRY...CATCH block, it propagates to the next higher TRY...CATCH block or to the calling application.

With proper use of TRY…CATCH blocks, you can make your SQL Server code more robust and fault-tolerant, ensuring that your database operations maintain integrity and reliability even under adverse conditions.

Also, Read: How do I write CRUD operations to modify data in SQL Server tables?

forum

How to handle errors using TRY...CATCH block in SQL Server?

Can you answer this question?

1 Answers

SQL Server Try….Catch Block