SQL Error Handling

Professional T-SQL shouldn't just crash. It should handle errors gracefully, roll back transactions, and log the failure. Modern SQL Server uses TRY...CATCH blocks, just like C# or Java.

1. The TRY...CATCH Pattern

Put your risky code inside BEGIN TRY. If an error occurs, the execution jumps immediately to BEGIN CATCH, where you can inspect the error using ERROR_MESSAGE() and ERROR_NUMBER().

BEGIN TRY
    BEGIN TRANSACTION
        -- Dangerous SQL here
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    PRINT ERROR_MESSAGE();
END CATCH

2. XACT_STATE() - The Pro Way

Sometimes, an error makes a transaction "Uncommittable." You can't commit it, and you can't even write a log entry using the same connection! XACT_STATE() tells you if the transaction is still alive (1), dead (-1), or non-existent (0), allowing you to handle the cleanup safely.

4. Interview Mastery

Q: "What is THROW vs RAISERROR?"

Architect Answer: "`RAISERROR` is the old way; it is flexible but buggy. `THROW` is the modern standard (introduced in 2012). Unlike RAISERROR, `THROW` obeys the SET XACT_ABORT ON setting and properly terminates the batch. It also allows you to 're-throw' an error from a CATCH block while preserving the original error line number, which is vital for debugging."