Transactions and Error Handling in SQL Server

What is a Transaction?

A transaction is a sequence of SQL operations executed as a single unit. If one part fails, the entire transaction can be rolled back.

Basic Transaction Example

Consider transferring money between two bank accounts:

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE ID = 1;

UPDATE Accounts
SET Balance = Balance + 100
WHERE ID = 2;

COMMIT;

If both updates succeed, the transaction is committed. But what if something fails?

Using ROLLBACK

If an error occurs, you can undo all changes:

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE ID = 1;

-- Simulate failure
ROLLBACK;

After ROLLBACK, no changes are saved.

Error Handling with TRY...CATCH

SQL Server provides a structured way to handle errors using TRY...CATCH.

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE Accounts
    SET Balance = Balance - 100
    WHERE ID = 1;

    UPDATE Accounts
    SET Balance = Balance + 100
    WHERE ID = 2;

    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;

    PRINT 'Transaction failed!';
END CATCH;

How It Works

• TRY block: Executes SQL statements
• CATCH block: Runs if an error occurs
• ROLLBACK: Reverts all changes

Capturing Error Details

You can retrieve detailed error information inside the CATCH block:

BEGIN CATCH
    ROLLBACK;

    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Best Practices

• Always use transactions for critical operations
• Keep transactions short to avoid locking issues
• Always handle errors with TRY...CATCH
• Never leave transactions open (always COMMIT or ROLLBACK)

Common Mistake

Forgetting to handle errors can leave your database in an inconsistent state:

-- BAD PRACTICE
BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;
-- If next query fails, money is lost!