Exception Handling in SQL Server by TRY…CATCH

Estimated study time: 10 minutes. Write safer T-SQL code by catching and handling errors properly.

SQL Server uses a TRY…CATCH construct to handle errors gracefully, similar to exception handling in programming languages like C# or Java.

Basic Syntax

BEGIN TRY
    -- Code that might throw an error
END TRY
BEGIN CATCH
    -- Code that runs if an error occurs
END CATCH

A Practical Example

BEGIN TRY
    UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2;

    IF @@ROWCOUNT = 0
        THROW 50000, 'Account not found.', 1;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER()    AS ErrorNumber,
        ERROR_MESSAGE()   AS ErrorMessage,
        ERROR_SEVERITY()  AS ErrorSeverity,
        ERROR_LINE()      AS ErrorLine;
END CATCH

Useful Error Functions Inside CATCH

• ERROR_NUMBER() — returns the error number.
• ERROR_MESSAGE() — returns the full error message text.
• ERROR_SEVERITY() — indicates how serious the error is.
• ERROR_LINE() — the line number where the error occurred.
• ERROR_PROCEDURE() — the stored procedure or trigger where it happened.

Combining with Transactions

TRY…CATCH is commonly paired with transactions so that if something fails midway, you can roll everything back cleanly.

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;
END CATCH