Error Handling in SQL

Effective error handling is crucial for building robust and reliable SQL applications. It allows you to gracefully manage unexpected situations, provide meaningful feedback to users, and maintain data integrity.

Understanding SQL Errors

SQL Server can encounter various types of errors during query execution:

Syntax Errors: Issues with the structure or grammar of your SQL statements. These are typically caught by the SQL parser before execution.
Runtime Errors: Errors that occur during the execution of a statement, such as attempting to divide by zero, violating a constraint, or referencing an invalid object.
Logical Errors: Errors where the SQL statement is syntactically correct but produces unintended or incorrect results due to flaws in the logic.

Error Reporting Mechanisms

SQL Server provides several ways to report and handle errors:

RAISERROR and THROW

These statements are used to explicitly raise errors or custom messages within your T-SQL code.

RAISERROR is a more versatile statement, allowing you to specify severity, state, and even insert custom parameters into messages. It's been a staple for many years.


-- Example using RAISERROR
IF EXISTS (SELECT 1 FROM YourTable WHERE SomeColumn IS NULL)
BEGIN
    RAISERROR('A required field cannot be null. Please check your input.', 16, 1);
    -- Additional error details can be provided as arguments
    -- RAISERROR('Value %s is out of range (0-100).', 16, 1, '500');
END

THROW, introduced in SQL Server 2012, is a simpler and more modern way to raise an error. It re-throws the last error encountered or allows you to raise a specific error with its number and message.


-- Example using THROW (re-throwing last error)
BEGIN TRY
    -- Some operation that might fail
    SELECT 1 / 0;
END TRY
BEGIN CATCH
    THROW; -- Re-throws the original error from the CATCH block
END CATCH

-- Example using THROW with custom error
BEGIN TRY
    -- Some operation
    IF @SomeCondition = 'Invalid'
        THROW 50001, 'Invalid input parameter provided.', 1;
END TRY
BEGIN CATCH
    -- Handle the custom error or re-throw
    PRINT ERROR_MESSAGE();
END CATCH

Note: While RAISERROR is still supported, THROW is generally recommended for new development due to its simpler syntax and better integration with the TRY...CATCH block.

TRY...CATCH Blocks

The TRY...CATCH construct provides a structured way to handle runtime errors gracefully within T-SQL stored procedures and batches.

The code within the TRY block is executed.
If any error occurs in the TRY block, execution immediately transfers to the CATCH block.
The CATCH block can then log the error, perform cleanup, or re-raise the error to the caller.


BEGIN TRY
    -- Code that might cause an error
    INSERT INTO Products (ProductName, Price) VALUES ('Widget', -10.00); -- Violates CHECK constraint
    PRINT 'Insert successful!';
END TRY
BEGIN CATCH
    -- Error handling logic
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Log the error to an error log table (recommended)
    INSERT INTO ErrorLog (ErrorMessage, ErrorSeverity, ErrorState, ErrorTime)
    VALUES (@ErrorMessage, @ErrorSeverity, @ErrorState, GETDATE());

    -- Re-throw the error to the calling application
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    -- Or use THROW for newer versions:
    -- THROW @ErrorSeverity, @ErrorMessage, @ErrorState;
END CATCH

Error Information Functions

Within a CATCH block, several built-in functions can be used to retrieve details about the error that occurred:

ERROR_NUMBER(): Returns the error number.
ERROR_SEVERITY(): Returns the severity level.
ERROR_STATE(): Returns the error state number.
ERROR_PROCEDURE(): Returns the name of the stored procedure or function where the error occurred.
ERROR_LINE(): Returns the line number within the code where the error occurred.
ERROR_MESSAGE(): Returns the complete error message text.

Tip: Always use ERROR_LINE() in conjunction with your code to pinpoint the exact location of the error during debugging.

Best Practices for Error Handling

Use TRY...CATCH: Implement TRY...CATCH blocks for critical sections of your T-SQL code.
Log Errors: Create a dedicated error logging table to record error details. This is invaluable for troubleshooting production issues.
Provide Meaningful Messages: When raising custom errors, ensure the messages are clear and actionable for developers or users.
Handle Specific Errors: If possible, catch specific error numbers or conditions to implement targeted recovery or reporting.
Re-throw or Return: Decide whether to re-throw the error to the calling application or handle it entirely within the procedure. Returning a status code or error indicator is also an option.
Maintain Data Integrity: Ensure that in case of an error, your transactions are rolled back to prevent partial data updates.
Avoid Suppressing Errors: Don't just swallow errors without logging or reporting them. This makes debugging extremely difficult.
Be Consistent: Adopt a consistent error handling strategy across your entire application.

Example: Robust Error Handling Procedure

Here's an example of a stored procedure that demonstrates good error handling practices:


CREATE PROCEDURE usp_UpdateProductPrice
    @ProductID INT,
    @NewPrice DECIMAL(10, 2)
AS
BEGIN
    SET NOCOUNT ON; -- Prevents sending DONE_IN_PROC messages

    -- Basic input validation
    IF @ProductID IS NULL OR @NewPrice IS NULL
    BEGIN
        THROW 51000, 'ProductID and NewPrice cannot be NULL.', 1;
        RETURN; -- Exit if validation fails
    END

    IF @NewPrice <= 0
    BEGIN
        THROW 51001, 'NewPrice must be a positive value.', 1;
        RETURN; -- Exit if validation fails
    END

    BEGIN TRY
        BEGIN TRANSACTION; -- Start transaction

        UPDATE Products
        SET Price = @NewPrice
        WHERE ProductID = @ProductID;

        IF @@ROWCOUNT = 0
        BEGIN
            -- No rows were updated, perhaps the ProductID doesn't exist.
            THROW 51002, 'Product with the specified ID not found.', 1;
        END

        COMMIT TRANSACTION; -- Commit if everything succeeded
        PRINT 'Product price updated successfully.';

    END TRY
    BEGIN CATCH
        -- Rollback transaction if an error occurred
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- Get error details
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();

        -- Log the error
        INSERT INTO ErrorLog (ErrorMessage, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, ErrorTime)
        VALUES (@ErrorMessage, @ErrorSeverity, @ErrorState, ERROR_PROCEDURE(), ERROR_LINE(), GETDATE());

        -- Re-throw the error to the caller
        THROW; -- Re-throws the original error caught
    END CATCH
END
GO

By implementing these strategies, you can significantly improve the reliability and maintainability of your SQL Server applications.

SQL Developer Guide

Error Handling in SQL

Understanding SQL Errors

Error Reporting Mechanisms

RAISERROR and THROW

TRY...CATCH Blocks

Error Information Functions

Best Practices for Error Handling

Example: Robust Error Handling Procedure