In SQL Server, TRY...CATCH is the standard mechanism for handling runtime errors. It allows you to “trap” errors that would normally stop your script, giving you a chance to log the error, rollback a transaction, or provide a custom message.

1. Basic Structure

The logic is split into two blocks:

  1. BEGIN TRY: Contains the code you want to run. If an error occurs here, execution jumps immediately to the CATCH block.
  2. BEGIN CATCH: Contains the code that handles the error (error logging, cleanup, etc.).

Example:

BEGIN TRY
    -- This will cause a 'Divide by zero' error
    SELECT 1 / 0;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorDescription;
END CATCH;

2. Essential Error Functions

Inside a CATCH block, you can use these built-in functions to get details about what went wrong:

Function Description
ERROR_MESSAGE() The actual text of the error message.
ERROR_NUMBER() The unique internal ID of the error.
ERROR_SEVERITY() The severity level (11-16 are typical user/code errors).
ERROR_PROCEDURE() The name of the stored procedure where the error happened.
ERROR_LINE() The line number inside the batch or procedure.

3. Using TRY…CATCH with Transactions

One of the most critical uses for TRY...CATCH is managing transactions. It ensures that if one part of a multi-step update fails, the whole process is rolled back to prevent data corruption.

Example:

BEGIN TRY
    BEGIN TRANSACTION;
        -- Step 1: Deduct money
        UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;
        
        -- Step 2: Add money (Assume this fails)
        UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 999; 

    COMMIT TRANSACTION;
    PRINT 'Transaction Committed Successfully';
END TRY
BEGIN CATCH
    -- Check if there is an active transaction to roll back
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    PRINT 'Transaction Failed. Error: ' + ERROR_MESSAGE();
END CATCH;

4. Nested TRY…CATCH

You can place a TRY...CATCH block inside another CATCH block. This is useful if your error-handling logic (like writing to a log table) might also fail.

BEGIN TRY
    -- Primary Code
END TRY
BEGIN CATCH
    BEGIN TRY
        -- Attempt to log error to a database table
        INSERT INTO ErrorLog (Msg) VALUES (ERROR_MESSAGE());
    END TRY
    BEGIN CATCH
        -- If logging fails, print to console as a backup
        PRINT 'Logging failed: ' + ERROR_MESSAGE();
    END CATCH
END CATCH;

5. THROW and RAISERROR

If you want to handle an error but then still “fail” the process so the calling application knows something went wrong, you use THROW inside your CATCH block.

BEGIN CATCH
    -- Log the error locally first
    INSERT INTO ErrorLog (Msg) VALUES (ERROR_MESSAGE());
    
    -- Re-raise the error to the calling app
    THROW; 
END CATCH;

Categorized in:

Stored Procedures,