SQL Server Documentation

Relational Databases | Stored Procedures

Stored Procedures and Error Handling

Stored procedures in SQL Server offer a powerful way to encapsulate complex logic and improve application performance. Robust error handling within stored procedures is crucial for maintaining data integrity and providing meaningful feedback to calling applications.

Introduction to Error Handling

When an error occurs during the execution of a stored procedure, SQL Server can either terminate the procedure or allow it to continue, depending on the error handling settings. Understanding how to manage these scenarios is key to building reliable database solutions.

The TRY...CATCH Construct

The primary mechanism for error handling in SQL Server is the TRY...CATCH block, introduced in SQL Server 2005. This construct allows you to gracefully handle runtime errors that occur within the TRY block.

Syntax:

BEGIN TRY
    -- Statements that might cause an error
END TRY
BEGIN CATCH
    -- Statements to execute if an error occurs
    -- Access error information using built-in functions
END CATCH;

Built-in Error Functions:

Example: Basic Error Handling

CREATE PROCEDURE usp_ProcessDataWithErrorHandling
        @InputValue INT
    AS
    BEGIN
        SET NOCOUNT ON;

        BEGIN TRY
            -- Attempt an operation that might fail (e.g., division by zero)
            DECLARE @Result INT;
            SET @Result = 10 / @InputValue;
            PRINT 'Operation successful. Result: ' + CAST(@Result AS VARCHAR(10));
        END TRY
        BEGIN CATCH
            -- Log the error or return error information
            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

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

            -- You can log this to an error table, raise a custom error, etc.
            RAISERROR ('An error occurred: %s', @ErrorSeverity, @ErrorState, @ErrorMessage);

            -- Optionally, rethrow the error to propagate it
            -- THROW;
        END CATCH;
    END;
GO

-- Example execution:
-- EXEC usp_ProcessDataWithErrorHandling 5; -- Success
-- EXEC usp_ProcessDataWithErrorHandling 0; -- Error

Controlling Error Behavior: @@ERROR vs. TRY...CATCH

Before SQL Server 2005, developers relied on the @@ERROR global variable. While still functional, TRY...CATCH is the preferred and more robust method as it provides structured exception handling and allows you to capture more detailed error information.

Best Practice: Always use TRY...CATCH blocks for error handling in your stored procedures. Avoid relying solely on @@ERROR.

Handling Errors in Transactions

When errors occur within a transaction, you need to decide whether to COMMIT or ROLLBACK the transaction. Typically, if an error occurs, you should ROLLBACK the transaction to maintain data consistency.

Example: Error Handling with Transactions

CREATE PROCEDURE usp_TransactionalOperation
        @Param1 INT,
        @Param2 INT
    AS
    BEGIN
        SET NOCOUNT ON;

        BEGIN TRANSACTION;

        BEGIN TRY
            -- Perform first operation
            UPDATE YourTable SET Column1 = @Param1 WHERE ID = 1;

            -- Perform second operation that might fail
            IF @Param2 = 0
                THROW 50001, 'Division by zero is not allowed.', 1;

            UPDATE YourTable SET Column2 = Column2 / @Param2 WHERE ID = 1;

            COMMIT TRANSACTION;
            PRINT 'Transaction committed successfully.';
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
                ROLLBACK TRANSACTION;

            -- Log or rethrow the error
            DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
            RAISERROR('Transaction failed: %s', 16, 1, @ErrorMessage);
            -- THROW; -- Uncomment to rethrow
        END CATCH;
    END;
GO

Custom Error Messages using RAISERROR and THROW

You can generate custom error messages using RAISERROR or THROW. THROW is generally preferred in SQL Server 2012 and later for its simplicity in re-throwing errors.

Example using THROW:

BEGIN TRY
    -- ... some code ...
    IF @SomeCondition = 1
        THROW 51000, 'A custom error condition has been met.', 1;
END TRY
BEGIN CATCH
    -- The THROW statement in CATCH re-throws the original error
    THROW;
END CATCH;

Logging Errors

A common practice is to log detailed error information to a dedicated error logging table. This table typically includes columns for the error number, severity, state, procedure name, line number, message, and the timestamp when the error occurred.

Error Logging Table Schema Example:

CREATE TABLE dbo.ErrorLog (
        ErrorLogID INT IDENTITY(1,1) PRIMARY KEY,
        ErrorNumber INT,
        ErrorSeverity INT,
        ErrorState INT,
        ErrorProcedure NVARCHAR(128),
        ErrorLine INT,
        ErrorMessage NVARCHAR(4000),
        ErrorDateTime DATETIME DEFAULT GETDATE()
    );
GO

Example Error Logging Procedure:

CREATE PROCEDURE usp_LogError
    AS
    BEGIN
        SET NOCOUNT ON;

        INSERT INTO dbo.ErrorLog (
            ErrorNumber,
            ErrorSeverity,
            ErrorState,
            ErrorProcedure,
            ErrorLine,
            ErrorMessage
        )
        VALUES (
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE()
        );
    END;
GO

-- Inside your CATCH block:
-- BEGIN CATCH
--     EXEC usp_LogError;
--     -- Optionally re-raise or return a generic message
--     THROW;
-- END CATCH;

Considerations for Production Environments

▲