MSDN Documentation

Error Handling in SQL Server Stored Procedures

Effective error handling is crucial for building robust and reliable SQL Server stored procedures. It allows you to gracefully manage unexpected situations, provide informative feedback to the caller, and prevent data corruption.

Introduction to Error Handling

SQL Server provides several mechanisms for handling errors that occur during the execution of Transact-SQL statements. These include:

• The @@ERROR global variable (though largely superseded by newer methods).
• The RAISERROR statement for raising custom errors.
• The TRY...CATCH block for structured exception handling.
• The THROW statement (SQL Server 2012 and later).

Using TRY...CATCH Blocks

The TRY...CATCH construct is the recommended approach for modern SQL Server applications. It provides a structured way to intercept and handle runtime errors.

The basic syntax is as follows:

BEGIN TRY
    -- Code that might raise an error
    -- e.g., SELECT 1/0;
END TRY
BEGIN CATCH
    -- Code to handle the error
    -- e.g., log the error, return an error message
END CATCH;
                

Common Error Handling Functions within CATCH

Inside the CATCH block, you can use several built-in functions to get information about the error:

• 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 where the error occurred.
• ERROR_MESSAGE(): Returns the complete error message text.

Example: Basic TRY...CATCH

CREATE PROCEDURE usp_ExampleErrorHandling
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- Intentionally cause a division by zero error
        DECLARE @Numerator INT = 10;
        DECLARE @Denominator INT = 0;
        DECLARE @Result INT;

        SET @Result = @Numerator / @Denominator;

        PRINT 'This line will not be reached.';
    END TRY
    BEGIN CATCH
        -- Capture error details
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

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

        -- Log the error (or re-raise it with more context)
        -- For demonstration, we'll just print the error
        PRINT 'An error occurred:';
        PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
        PRINT 'Message: ' + @ErrorMessage;
        PRINT 'Severity: ' + CAST(@ErrorSeverity AS VARCHAR(2));
        PRINT 'State: ' + CAST(@ErrorState AS VARCHAR(2));
        PRINT 'Procedure: ' + ISNULL(ERROR_PROCEDURE(), 'N/A');
        PRINT 'Line: ' + CAST(ERROR_LINE() AS VARCHAR(10));

        -- Optionally re-raise the error if the caller needs to handle it
        -- RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
END;
GO

-- Execute the procedure to test error handling
EXEC usp_ExampleErrorHandling;
                

Using RAISERROR

The RAISERROR statement allows you to generate a custom error message and return it to the client. This is useful for signaling specific business logic violations or conditions that require attention.

RAISERROR can be used within a TRY block to signal an error condition that is then caught by a CATCH block, or directly within your procedural logic.

RAISERROR ('Your custom error message here.', 16, 1); -- Severity 16 is a general error
                

When used with severity levels 11 through 19, RAISERROR is treated as an error and will cause execution to jump to the nearest CATCH block if one is active.

Using THROW (SQL Server 2012 and Later)

The THROW statement is a simpler and more modern way to raise an error or re-throw an exception. It is the preferred method for raising exceptions in SQL Server 2012 and later.

-- Raising a specific error
THROW 50001, 'Invalid input parameter detected.', 1;

-- Re-throwing the last error
BEGIN TRY
    -- Some operation
END TRY
BEGIN CATCH
    THROW; -- Re-throws the caught exception
END CATCH;
                

Using THROW without parameters inside a CATCH block will re-throw the original error, preserving its original error number, severity, state, and message.

Best Practices for Error Handling

• Use TRY...CATCH: It's the most robust and recommended method.
• Log Errors: Always implement a logging mechanism to record error details for debugging and auditing. This could be a dedicated error log table.
• Provide Informative Messages: Custom error messages should be clear and helpful to the user or calling application.
• Appropriate Severity Levels: Use severity levels correctly to indicate the impact of the error.
• Return Error Codes or Status: For applications that don't use TRY...CATCH directly, consider returning specific error codes or status values via output parameters.
• Transaction Management: Ensure that your error handling logic correctly manages transactions, often by rolling back the transaction if an error occurs.
• Avoid Suppressing Errors Unnecessarily: Only suppress errors when you have a specific reason and a clear strategy for handling them.