Error Handling

Introduction

Errors are an inevitable part of database management. In Microsoft SQL Server (MS SQL), error handling is essential for ensuring your SQL scripts handle unexpected events gracefully, such as data entry errors, connection issues, or logic problems. Proper error handling improves application stability and helps maintain data integrity.

MS SQL provides various mechanisms to capture and manage errors, including:

• TRY...CATCH blocks
• RAISEERROR
• THROW

In this section, we’ll explore these tools and techniques, using practical examples relevant to customer management.

Understanding TRY...CATCH

A TRY...CATCH block in MS SQL is the primary mechanism for handling errors. Code inside the TRY block is executed, and if an error occurs, the control moves to the CATCH block, where the error can be handled appropriately.

Syntax:

BEGIN TRY
    -- Statements to attempt
END TRY
BEGIN CATCH
    -- Error handling logic
END CATCH

In the CATCH block, you can use system functions to get more details about the error, such as:

• ERROR_MESSAGE(): Returns the error message.
• ERROR_SEVERITY(): Returns the severity level.
• ERROR_LINE(): Returns the line number where the error occurred.

Example: Updating customer data and handling errors.

BEGIN TRY
    UPDATE Customers
    SET Email = 'newemail@example.com'
    WHERE customer_id = 101;
END TRY
BEGIN CATCH
    PRINT 'Error: ' + ERROR_MESSAGE();
END CATCH;

If an error occurs during the update, the error message will be printed without stopping the execution.

RAISEERROR

The RAISEERROR statement is used to explicitly throw an error in SQL Server. This can be useful for creating custom error messages and triggering them under specific conditions.

Syntax:

RAISEERROR (message_string, severity, state);

• message_string: The error message.
• severity: The severity level of the error (1-25).
• state: A number to describe the error's state.

Example: Custom error for invalid email update.

IF NOT EXISTS (SELECT 1 FROM Customers WHERE email = 'test@example.com')
BEGIN
    RAISEERROR ('Invalid email address.', 16, 1);
END;

Here, if no customer is found with the specified email, a custom error message is raised.

THROW Statement

Introduced in SQL Server 2012, the THROW statement is a more modern way to raise exceptions. Unlike RAISEERROR, THROW does not require defining a severity level or state, making it simpler to use.

Syntax:

THROW [error_number, message, state];

Example: Using THROW in customer management.

IF NOT EXISTS (SELECT 1 FROM Customers WHERE customer_id = 200)
BEGIN
    THROW 50001, 'Customer not found.', 1;
END;

This query throws an error if the customer with customer_id = 200 does not exist.

Capturing Error Details in the CATCH Block

You can capture and log specific error details in the CATCH block using the built-in system functions. These details can help diagnose and fix errors effectively.

Example: Log detailed error information when updating a customer record.

BEGIN TRY
    UPDATE Customers
    SET Email = 'updatedemail@example.com'
    WHERE customer_id = 5;
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
    SET @ErrorMessage = ERROR_MESSAGE();
    SET @ErrorSeverity = ERROR_SEVERITY();
    SET @ErrorState = ERROR_STATE();

    PRINT 'Error occurred: ' + @ErrorMessage;
    PRINT 'Severity: ' + CAST(@ErrorSeverity AS NVARCHAR);
    PRINT 'State: ' + CAST(@ErrorState AS NVARCHAR);
END CATCH;

This query updates a customer’s email. If an error occurs, the error message, severity, and state are printed.

Best Practices

• Use meaningful error messages: Avoid generic errors. Provide users and developers with detailed error messages that are useful for diagnosing the issue.

• Log errors: Always log critical errors using tables or external logging mechanisms to help in debugging and monitoring.

• Handle specific errors: Whenever possible, handle different errors differently based on their severity or nature. For example, handle connection issues separately from data validation errors.

• Transactions for critical operations: When performing multiple operations that must succeed as a whole, always use transactions to prevent data inconsistencies.

• Use THROW for re-throwing errors: If you need to re-raise an error in the CATCH block, use THROW to maintain the original error context.

Conclusion

Error handling in MS SQL is essential to ensure that your database operations are resilient and secure. By using TRY...CATCH, RAISEERROR, THROW, and transactions, you can effectively manage and mitigate errors. Whether updating customer records, processing orders, or dealing with invalid inputs, robust error handling ensures a smooth user experience and maintains data consistency.

Asked in Interview

• What are common errors that occur in SQL, and how do you handle them?
• How does the TRY...CATCH block work in SQL Server?
• How do you manage transactions when an error occurs?
• How does the RAISEERROR statement work in SQL Server?
• Can you use nested TRY...CATCH blocks in SQL Server?
• What are some best practices for error handling in SQL?
• How do you log errors in SQL Server?