Error Handling in SQL with TRY...CATCH

TRY…CATCH is a control structure in T-SQL that allows handling errors that may occur during code execution.

Until now, if one of our scripts failed (for example, a division by zero or a foreign key violation), SQL Server would show a red message and stop the execution.

But that… doesn’t look very professional, does it? It’s not good to let the code “explode” uncontrollably. We need to capture the error, clean it up, and decide what to do.

For this, T-SQL implements the TRY...CATCH structure (very similar to the one found in languages like C#, JavaScript, or Python).

Basic Structure

Its operation is simple:

That is, the basic syntax is as follows:

BEGIN TRY
    -- Code that could generate an error
END TRY
BEGIN CATCH
    -- Error handling
END CATCH;

Basic Example

Imagine we have a Products table and we want to insert a new product. If the product already exists (violates a UNIQUE constraint), we want to handle the error in a controlled manner:

BEGIN TRY
    INSERT INTO Productos (ID, Nombre, Precio)
    VALUES (1, 'Laptop', 1200);
END TRY
BEGIN CATCH
    PRINT 'Error: Could not insert the product.';
    PRINT 'Error message: ' + ERROR_MESSAGE();
END CATCH;

In this example, if the product with ID = 1 already exists, the error is caught in the CATCH block and an informative message is displayed.

Getting Error Information

Knowing there was an error is good, but we also need to know what happened. Inside the CATCH block, we have access to special system functions that give us the details of the crash:

For example,

BEGIN TRY
    -- We try to insert a duplicate into a PK
    INSERT INTO Clientes (ClienteID, Nombre) VALUES (1, 'Juan');
    INSERT INTO Clientes (ClienteID, Nombre) VALUES (1, 'Pedro'); -- Fails here
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS NumeroError,
        ERROR_MESSAGE() AS Mensaje,
        ERROR_LINE() AS Linea;
END CATCH

Error Handling in Transactions

One of the most common uses of TRY...CATCH is to protect data integrity when we use transactions.

If you open a transaction, make two changes, and the third one fails… what happens to the open transaction? It gets “stuck”, locking tables and consuming resources.

We must use the CATCH block to do the ROLLBACK (undo) automatically if something goes wrong.

BEGIN TRY
    BEGIN TRANSACTION; -- Open transaction

        -- Step 1: Subtract Stock
        UPDATE Productos SET Stock = Stock - 1 WHERE ID = 100;

        -- Step 2: Generate a forced error (e.g., CHECK constraint)
        UPDATE Ventas SET Cantidad = -5 WHERE ID = 999; 

    COMMIT TRANSACTION; -- If all goes well, we save
    PRINT 'Sale completed successfully';
END TRY
BEGIN CATCH
    -- Something failed!
    
    -- We check if there is an open transaction to undo it
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
        PRINT 'Error detected. All changes have been undone.';
    END

    -- Show the original error
    SELECT ERROR_MESSAGE() AS MotivoFallo;
END CATCH

Rethrowing the Error: THROW

When you catch an error with CATCH, for SQL Server the error has already been handled. The script ends “successfully” (exit code 0) even though you printed an error message.

This is dangerous because the application that called the SQL (your backend in .NET, Java, etc.) will think everything went well.

To notify the outside world that there was a problem, we must rethrow the error using THROW.

BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

    -- Log the error in a log table (optional)
    INSERT INTO LogErrores (Mensaje) VALUES (ERROR_MESSAGE());

    -- We throw the error again to the application!
    -- Syntax: THROW [Number], [Message], [State];
    -- Or simply ;THROW to rethrow the original one.
    
    ;THROW; 
END CATCH