Lesson 17 — Reliability

Error Handling with TRY...CATCH

T-SQL's TRY...CATCH construct lets you gracefully handle runtime errors instead of letting them halt execution and expose raw error messages.

TRY...CATCH Basics

Code that might fail is placed inside a TRY block. If an error occurs, control immediately passes to the CATCH block, where you can log the error, roll back a transaction, or return a friendlier message.

T-SQL — TRY...CATCH with a transaction 
BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE Accounts SET Balance = Balance - 500 WHERE AccountId = 1;
    UPDATE Accounts SET Balance = Balance + 500 WHERE AccountId = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    SELECT ERROR_MESSAGE() AS ErrorMessage, ERROR_NUMBER() AS ErrorNumber;
END CATCH;

Common Mistakes

  • Forgetting to check whether a transaction is still open in the CATCH block before rolling it back, which can itself raise an error.
  • Catching every error identically without distinguishing between recoverable and unrecoverable failures.
  • Swallowing an error silently in CATCH without logging it anywhere, making failures invisible and hard to diagnose later.
  • Assuming TRY...CATCH catches every kind of error — some severe system-level errors cannot be caught this way.

Professional Tip

Always log the output of ERROR_MESSAGE(), ERROR_NUMBER(), and ERROR_LINE() inside a CATCH block, even during development. Silently discarding error details turns every production issue into a much longer investigation.

Your Turn

Wrap the money-transfer transaction from the previous lesson in TRY...CATCH, rolling back and returning the error details if anything fails.

Mini Quiz

What happens to execution when an error occurs inside a TRY block?