How to Use TRY CATCH in SQL Server

When working with Microsoft SQL Server, things don’t always go as planned. Maybe a query fails because of a constraint, a divide-by-zero error, or a missing record. Instead of letting your script crash, you can handle these situations gracefully using TRY…CATCH blocks.

In this post, I’ll show you how to use TRY CATCH in SQL Server, how to apply it inside stored procedures, and how to manage exception handling in stored procedure in SQL Server using practical examples from the Northwind database.

Why Use TRY…CATCH in SQL Server?

The TRY…CATCH block in SQL Server works much like error handling in other programming languages. It allows you to:

• Catch errors instead of letting them stop execution
• Log or display friendly error messages
• Roll back transactions safely when something fails

This is especially useful in production environments where you don’t want one small issue to interrupt an entire batch or stored procedure.

Basic Syntax of TRY…CATCH

Here’s the basic structure:

BEGIN TRY
    -- SQL statements that might fail
END TRY
BEGIN CATCH
    -- Code to handle the error
END CATCH

When an error occurs inside the TRY block, SQL Server automatically jumps to the CATCH block.

How to Use TRY CATCH in SQL Server?

Let’s look at an example using the Northwind database. Suppose you accidentally try to divide by zero:

BEGIN TRY
    SELECT 100 / 0 AS Result;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH

What happens here? Instead of the query failing, SQL Server moves to the CATCH block and returns the error number and message, without stopping your query. Here is what it looks like in SQL Server Management Studio:

Using TRY…CATCH with Transactions

TRY…CATCH is especially powerful when used with transactions. For example, let’s say you’re updating the Orders table in Northwind:

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE Orders
    SET Freight = Freight * 1.1
    WHERE ShipCountry = 'Germany';

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

If something goes wrong (like a constraint violation) the transaction is rolled back automatically, and the error message is captured.

How to Use TRY CATCH in SQL Server Stored Procedure

You can use TRY…CATCH inside a stored procedure to make your database logic safer and more reliable.

Here’s an example:

CREATE PROCEDURE UpdateCustomerCity
    @CustomerID NVARCHAR(5),
    @NewCity NVARCHAR(15)
AS
BEGIN
    BEGIN TRY
        -- Check if @NewCity is NULL before updating
        IF @NewCity IS NULL
        BEGIN
            RAISERROR ('City value cannot be NULL.', 16, 1);
            RETURN;
        END

        UPDATE Customers
        SET City = @NewCity
        WHERE CustomerID = @CustomerID;

        IF @@ROWCOUNT = 0
        BEGIN
            RAISERROR ('Customer not found.', 16, 1);
            RETURN;
        END

        PRINT 'Customer city updated successfully.';
    END TRY
    BEGIN CATCH
        SELECT 
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END;

You can then run the stored procedure like this:

EXEC UpdateCustomerCity 'TEST123', 'Zagreb';

If an invalid CustomerID is provided or another issue occurs, the procedure won’t break. It will catch and report the error:

Similar thing will happen if we try to use NULL as a City when executing the stored procedure:

Exception Handling in Stored Procedure in SQL Server

When it comes to exception handling in stored procedure in SQL Server, the key is to combine TRY…CATCH with proper transaction control. You want to ensure that either all changes succeed or none do.

Here’s a safer version of the stored procedure with a transaction included:

CREATE OR ALTER PROCEDURE UpdateCustomerWithTransaction
    @CustomerID NVARCHAR(5),
    @NewCity NVARCHAR(15)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Check for NULL city value
        IF @NewCity IS NULL
        BEGIN
            RAISERROR ('City value cannot be NULL.', 16, 1);
            ROLLBACK TRANSACTION;
            RETURN;
        END

        -- Perform the update
        UPDATE Customers
        SET City = @NewCity
        WHERE CustomerID = @CustomerID;

        -- Check if any rows were updated
        IF @@ROWCOUNT = 0
        BEGIN
            RAISERROR ('Customer not found.', 16, 1);
            ROLLBACK TRANSACTION;
            RETURN;
        END

        COMMIT TRANSACTION;
        PRINT 'Customer city updated successfully.';
    END TRY
    BEGIN CATCH
        -- Roll back any changes if an error occurs
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        PRINT 'Error occurred while updating customer data.';
        SELECT 
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END;

This way, if anything goes wrong during the update, the database rolls back to its original state. Here is what the stored procedure looks like in SSMS:

Common Error Functions in SQL Server

Inside a CATCH block, you can use several system functions to get detailed information about the error:

• ERROR_NUMBER() – The error number.
• ERROR_MESSAGE() – The actual error message text.
• ERROR_LINE() – The line number where the error occurred.
• ERROR_PROCEDURE() – The name of the stored procedure where it happened.

These functions help with debugging and logging errors in a more structured way.

Final Thoughts

Now you know how to use TRY CATCH in SQL Server to manage errors gracefully. You’ve also learned how to use TRY CATCH in SQL Server stored procedure for safer database operations and how to handle exceptions with rollback logic.

Error handling is one of those small things that make a huge difference in real-world applications. With TRY…CATCH, your SQL code becomes more professional and reliable.

If you want to learn more about SQL, check out my recent blog post about Transaction Handling in SQL Server. You can also learn more about TRY…CATCH on Microsoft’s official page.

There is also a great book about programming SQL queries. You can check it out here: T-SQL Fundamentals (Developer Reference)