SQL Server Try Catch

Error handling is a critical tool for us developers as it allows us to anticipate and handle potential errors. This ensures that apps do not crash when they encounter an unexpected condition but can perform a given action or terminate gracefully.

In SQL Server, the try-catch block provides us with a powerful error handling mechanism to manage errors that can occur during the execution of T-SQL queries.

Using this try-catch block, we can implement mechanisms for handling errors such as graceful termination, gathering information, and more.

In this tutorial, we are going to learn all about the SQL Server Try/Catch blocks. How it works, how to use, things to note when using it and more.

Syntax of SQL Server Try-Catch

The basic syntax of a Try-Catch block in SQL Server is as shown below

BEGIN TRY
    -- Main T-SQL code block
END TRY
BEGIN CATCH
    -- Error handling code
END CATCH

• The BEGIN TRY block contains the main T-SQL code that may potentially raise an error.
• The BEGIN CATCH block contains the error handling code that executes when an error occurs in the TRY block.

SQL Server Error Types

Before we jump into the functionality and usage of the try/catch block, let us discuss the various types of errors that occurs in a SQL Server database.

• Compile-Time Errors - These errors occur during the parsing and compilation of SQL code. Try-Catch cannot handle compile-time errors.
• Run-Time Errors - These errors occur during the execution of SQL code and are the focus of Try-Catch.
  • User-Defined Errors - These are type of errors that we raise manually using the raiseerror statement or using system functions.
  • System Errors - As the name says, system errors are errors raised by the SQL Server Engine itself due to rule violations and unxpected results. For example, constraint violation, data type mismatches, incompatible data layout and more.

SQL Server Try/Catch - Exception Handling

Take a look at the example code below that demonstrates the basic usage of the try/catch block in SQL Server.

BEGIN TRY
    DECLARE @result FLOAT;
    SET @result = 5 / 0; -- This will raise a divide-by-zero error
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState;
END CATCH

In the example above, we can break down the code as shown:

• The TRY block attempts to divide 5 by 0, which triggers an error.
• The CATCH block captures error information using functions like ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), and ERROR_STATE().

As you can guess, the code above uses the try/catch block to handle a divide-by-zero error.

Handling Multiple Exceptions

We can also handle multiple types of exceptions by adding multiple CATCH blocks with specific error conditions. The syntax is as shown:

BEGIN TRY
    -- T-SQL code
END TRY
BEGIN CATCH
    -- Error handling for a specific error
END CATCH
BEGIN CATCH
    -- Error handling for another specific error
END CATCH

SQL Server will execute the first CATCH block that matches the error condition.

Logging and Raising Custom Errors

We can log error details to a table or a file within the CATCH block for later analysis. We can also raise custom errors by using the raiseerror statement which allows us to provide custom messages and feedback to the caller.

An example is as shown:

BEGIN TRY
    -- T-SQL code
END TRY
BEGIN CATCH
    -- Log error details to a table
    INSERT INTO ErrorLog (ErrorDateTime, ErrorMessage)
    VALUES (GETDATE(), ERROR_MESSAGE());

    -- Raise a custom error
    RAISEERROR('An error occurred. Please contact admin.', 16, 1);
END CATCH

In this example, we create a log entry into the table with details such as the error date time, and error message. We also ensure to use the raiseerror statement to create a custom error message to the user.

Nested Try-Catch Blocks

We can also have nested try/catch blocks to handle errors at different levels of granularity. This allows for more precise error handling.

An example syntax is as shown:

BEGIN TRY
    -- Outer Try-Catch block
    BEGIN TRY
        -- Inner Try-Catch block
    END TRY
    BEGIN CATCH
        -- Inner error handling
    END CATCH
END TRY
BEGIN CATCH
    -- Outer error handling
END CATCH

Examples

Let us look at two real-world examples to illustrate the practical use of SQL Server Try-Catch.

Inserting Data

BEGIN TRY
    INSERT INTO Employees (EmployeeID, EmployeeName) VALUES (123, 'John Doe');
END TRY
BEGIN CATCH
    INSERT INTO ErrorLog (ErrorDateTime, ErrorMessage)
    VALUES (GETDATE(), ERROR_MESSAGE());
END CATCH

In this example, we attempt to insert data into the Employees table. If an error occurs, it’s logged to the ErrorLog table.

Complex Transaction

BEGIN TRY
    BEGIN TRANSACTION;
    
    -- Multiple SQL statements
    
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    
    INSERT INTO ErrorLog (ErrorDateTime, ErrorMessage)
    VALUES (GETDATE(), ERROR_MESSAGE());
END CATCH

In this example, we use Try-Catch to handle errors within a complex transaction. If an error occurs, the transaction is rolled back, and the error is logged.

Conclusion

In this post, we explored everything you need to know about the SQL Server try/catch block.