All 7 Error Handling with TRY CATCH questions
More in Error Handling with TRY CATCH
How does a TRY CATCH block work in SQL Server, and why is it useful? What error information functions are available inside a CATCH block to help you understand what went wrong? What is the difference between using THROW and RAISERROR to raise a custom error in SQL Server? What types of errors cannot be caught by a TRY CATCH block in SQL Server? How would you build a centralized, reusable error logging procedure that captures detailed error information whenever it is called from within a CATCH block? How do nested TRY CATCH blocks work, and when would you use them in a complex stored procedure?
Advertisement
Back to results for “Error Handling”
SQL Server › Error Handling with TRY CATCH

How do you combine a TRY CATCH block with a transaction to ensure the database is properly rolled back if an error occurs partway through?

Intermediate

Answer

You begin a transaction inside the TRY block, and if an error occurs and control moves to the CATCH block, you check whether a transaction is still open using XACT_STATE and roll it back if necessary, ensuring no partial, inconsistent changes are left in the database after an error interrupts a multi step operation.
BEGIN TRY
  BEGIN TRANSACTION;
  UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
  UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 2;
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION;
  THROW;
END CATCH;
Real-world example A bank transfer procedure rolls back both account updates if either one fails partway through, guaranteeing the customer's money is never lost or duplicated due to an unexpected error interrupting the transfer.

Common follow-ups: What does XACT_STATE actually return, and why is checking it important?;What is the difference between using THROW and RAISERROR to re-raise an error?

Transactions & ACID;Isolation & Locking
All 7 Error Handling with TRY CATCH questions