SQL Server Documentation

Error Handling in SQL Server Stored Procedures

Robust error handling is crucial for any database application. SQL Server provides several mechanisms to detect, report, and manage errors that occur during the execution of stored procedures. Implementing effective error handling ensures data integrity, provides valuable debugging information, and improves the overall reliability of your database solutions.

Why is Error Handling Important?

• Data Integrity: Prevents partial transactions or corrupt data from being committed.
• User Experience: Provides meaningful feedback to users instead of cryptic error messages.
• Troubleshooting: Simplifies debugging by identifying the source and nature of errors.
• Application Stability: Prevents unexpected application crashes due to unhandled database errors.

Common Error Handling Mechanisms

1. @@ERROR System Function

The @@ERROR function returns the error number for the last Transact-SQL statement executed. A return value of 0 indicates no error. While simple, it's important to check @@ERROR immediately after the statement you are monitoring, as any subsequent statement will overwrite its value.

-- Attempt an operation that might fail (e.g., inserting a duplicate key)
INSERT INTO MyTable (ID, Name) VALUES (1, 'Test Item');

IF @@ERROR <> 0
BEGIN
    -- An error occurred. Log it or handle it.
    PRINT 'An error occurred during insertion. Error number: ' + CAST(@@ERROR AS VARCHAR);
    -- You might want to ROLLBACK TRANSACTION here if in a transaction
END
ELSE
BEGIN
    PRINT 'Insertion successful.';
END
                

2. TRY...CATCH Blocks (SQL Server 2005 and later)

The TRY...CATCH construct is the most powerful and recommended method for error handling in modern SQL Server development. It allows you to define a block of code that might raise an error (the TRY block) and a separate block that is executed if any error occurs within the TRY block (the CATCH block).

Within the CATCH block, you can use the following system functions to get details about the error:

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

BEGIN TRY
    -- Code that might cause an error
    DECLARE @x INT;
    SET @x = 1 / 0; -- This will raise a divide by zero error
    PRINT 'This line will not be reached.';
END TRY
BEGIN CATCH
    -- Error handling logic
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;

    -- It's good practice to wrap error handling in a transaction
    -- If an error occurs, ROLLBACK the transaction
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Optionally, re-throw the error for the calling application to handle
    -- THROW;
END CATCH

-- If code outside TRY...CATCH continues, it will execute
PRINT 'Execution continues after CATCH block.';
                

3. RAISERROR and THROW

RAISERROR and THROW are used to explicitly generate errors within your stored procedures. This is useful for validating input, signaling specific conditions, or custom error reporting.

RAISERROR

RAISERROR can generate user-defined error messages. It allows you to specify the message text, severity, and state.

IF NOT EXISTS (SELECT 1 FROM Users WHERE UserID = @UserID)
BEGIN
    RAISERROR('User ID %d not found.', 16, 1, @UserID);
    -- If severity is 11 or higher, it will trigger a CATCH block if present
END
                

THROW

THROW (introduced in SQL Server 2012) is a simpler and more modern alternative to RAISERROR for raising errors. It can re-throw the current error or raise a new, specified error.

BEGIN TRY
    -- Some operation
    IF @SomeConditionIsFalse
    BEGIN
        THROW 50001, 'A custom error occurred due to specific conditions.', 1;
    END
END TRY
BEGIN CATCH
    -- Log error details using ERROR_ functions
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
    -- Re-throw the error to propagate it
    THROW;
END CATCH
                

Best Practices for Error Handling

• Use TRY...CATCH: For all new development, TRY...CATCH is the preferred method.
• Be Specific: Clearly define what conditions should trigger an error and what messages to provide.
• Log Errors: Implement a mechanism to log detailed error information to a table or an event log for later analysis.
• Consistent Severity: Use appropriate severity levels for errors. High severity (16+) often terminates the batch.
• Transaction Management: Always ensure transactions are properly handled (committed or rolled back) within error handling logic.
• Informative Messages: Provide user-friendly error messages when possible, but include technical details in logs.
• Avoid Suppressing Errors: Don't just silently ignore errors. Either handle them gracefully or re-throw them to be handled elsewhere.
• Test Thoroughly: Test your error handling code under various failure scenarios.