4.2.3. T-SQL Error Handling (TRY/CATCH)

💡 First Principle: T-SQL transactions require explicit error handling to ensure data consistency. Without TRY/CATCH, partial failures can leave your database in an inconsistent state—some records inserted, others not.

Scenario: An INSERT operation must be atomic—either all records insert successfully, or none do. Without proper error handling, partial inserts can corrupt data.

Proper Transaction Pattern

BEGIN TRY
    BEGIN TRANSACTION;
    
    INSERT INTO dbo.Customers (FirstName, LastName)
    VALUES (N'Jeff', N'Price');
    
    -- If no error, commit
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- On error, rollback and report
    SELECT ERROR_NUMBER() AS ErrorNumber, 
           ERROR_MESSAGE() AS ErrorMessage;
    ROLLBACK TRANSACTION;
END CATCH;

⚠️ Exam Trap: COMMIT without BEGIN TRANSACTION attempts to commit a transaction that was never started. Always pair BEGIN TRANSACTION with COMMIT/ROLLBACK. This is a common distractor in exam questions.