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

Question

Ashutosh Kumar Verma · 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
and 
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 Notificationand 
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 blocksand 
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.
and 
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.
and 
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?

Sandra Emily

Can you answer this question?

1 Answers

SQL Server Try….Catch Block

Liked By