How do you use the TRY...CATCH block to handle errors in SQL Server?
HenryPk
Posted by HenryPk
Last Updated: July 18, 2024
error handling
In SQL Server, the TRY...CATCH construct is used to handle errors gracefully in your T-SQL code. When an error occurs in the TRY block, control is transferred to the CATCH block, where you can manage the error, log it, or perform other necessary actions. Here's how to use it effectively:
Basic Syntax
BEGIN TRY
    -- Code that might cause an error
END TRY
BEGIN CATCH
    -- Code to handle the error
END CATCH
Example Usage
Here’s an example of how to use the TRY...CATCH block in SQL Server: 
BEGIN TRY
    -- Attempt to divide by zero, which will cause an error
    DECLARE @result INT;
    SET @result = 10 / 0; -- This will raise a divide-by-zero error
END TRY
BEGIN CATCH
    -- Handle the error
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
    PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR);
    PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR);
    PRINT 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR);
    PRINT 'Error Procedure: ' + ISNULL(ERROR_PROCEDURE(), 'N/A');
    PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR);
END CATCH;
Explanation
- BEGIN TRY / END TRY: This block contains the code that you want to execute. If any error occurs, control is passed to the CATCH block. - BEGIN CATCH / END CATCH: This block gets executed when an error occurs in the TRY block. You can use several built-in functions to capture details about the error: - ERROR_MESSAGE(): Returns the error message. - ERROR_NUMBER(): Returns the error number. - ERROR_SEVERITY(): Returns the error severity. - ERROR_STATE(): Returns the state number of the error. - ERROR_PROCEDURE(): Returns the name of the stored procedure or trigger that generated the error. - ERROR_LINE(): Returns the line number at which the error occurred.
Important Notes
- The CATCH block runs only if an error with a severity level of 11 or higher occurs. - Not every error can be caught with TRY...CATCH. Certain errors, such as compile-time errors and those that cause the server to abort processing (like hardware failures), cannot be handled this way. - The control flow does not exit the outer scope. You can still have additional statements after the CATCH block, which will execute regardless of whether an error occurred.
Example with Transaction Handling
You can also use TRY...CATCH with transactions to ensure that your database state remains consistent. Here's an example: 
BEGIN TRY
    BEGIN TRANSACTION;

    -- Some SQL statements that might cause an error
    INSERT INTO dbo.MyTable (Column1, Column2) VALUES (1, 'Value');
    -- Potentially risky statement
    INSERT INTO dbo.MyTable (Column1, Column2) VALUES (2, NULL); -- Assume this might raise an error

    COMMIT TRANSACTION; -- Only commit if everything is successful
END TRY
BEGIN CATCH
    -- Rollback the transaction if an error occurs
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Handle the error
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;
In this example, if any error occurs before the COMMIT TRANSACTION, the transaction is rolled back in the CATCH block. This ensures that the database remains consistent, and no partial updates are saved. Using TRY...CATCH is crucial for robust SQL programming, as it allows you to handle errors in a controlled manner.
Default Image