Обработка ошибок с TRY...CATCH

Приложения рабочей базы данных должны корректно обрабатывать непредвиденные ситуации. Деление на ноль, нарушение ограничений, истекание времени ожидания подключения и недопустимые данные могут вызывать ошибки. Необработанные ошибки приводят к неясным сообщениям об ошибках, неполным транзакциям или сбою приложения. Правильная обработка ошибок гарантирует, что код T-SQL терпит предсказуемые неудачи и предоставляет информативную обратную связь.

Операции базы данных взаимодействуют с несколькими пользователями, внешними системами и непредсказуемыми входными данными одновременно. В отличие от кода приложения, который может восстановиться после неудачной операции путем повторных попыток, ошибки базы данных могут оставлять данные в неконсистентном состоянии: некоторые строки вставлены, а другие нет, а блокировки удерживаются на неопределенный срок. Обработка ошибок преобразует эти хаотические режимы сбоя в управляемые, прогнозируемые ответы.

Хорошо разработанная обработка ошибок улучшает код несколькими способами:

• Защита целостности данных: Если операция прерывается частично, правильная обработка ошибок гарантирует, что либо все изменения фиксируются вместе, либо не сохраняются вовсе. Без этого многоэтапный процесс может оставить базу данных с осиротевшими записями, несогласованными суммами или сломанными связями.

• Эффективность отладки: запись сведений об ошибке, таких как номер строки, имя процедуры и конкретное сообщение об ошибке, упрощает устранение неполадок. Вместо поиска по журналам расплывчатых сбоев можно точно определить, где и почему произошла ошибка.

• Взаимодействие с пользователем. Приложения могут отображать значимые сообщения, такие как "Идентификатор продукта не существует" вместо ошибок криптографической базы данных. Это помогает пользователям понять, что пошло не так и как исправить его.

• Оперативная видимость: Ведение журнала ошибок в выделенной таблице создает аудиторский след, который помогает выявлять шаблоны, такие как повторяющиеся нарушения ограничений, указывающие на ошибку, или ошибки из-за истечения времени ожидания, которые указывают на проблемы с производительностью.

• Плавная деградация: в случае сбоя одной операции обработка ошибок позволяет остальному коду продолжать работу или выполнять альтернативные действия, а не завершать весь пакет или хранимую процедуру аварийно.

Реализация обработки ошибок T-SQL

T-SQL обеспечивает структурированную обработку ошибок с помощью TRY...CATCH блоков, аналогичных обработке исключений на других языках программирования. При возникновении ошибки в блоке TRY выполнение передается в блок CATCH, где можно соответствующим образом обработать ошибку.

BEGIN TRY
    -- TRY block contains code that might cause an error
    -- If an error occurs here, execution jumps to the CATCH block
    SELECT 1/0;  -- This causes a division by zero error
END TRY
BEGIN CATCH
    -- CATCH block handles the error
    -- This code runs only if an error occurred in the TRY block
    PRINT 'An error occurred';
END CATCH;

Без обработки ошибок тот же код завершается сообщением об ошибке:

SELECT 1/0;  -- Msg 8134: Divide by zero error encountered

Получение сведений об ошибке

В блоке CATCH SQL Server предоставляет следующие функции для получения сведений об ошибке, которая произошла:

В следующем примере показано, как записывать сведения об ошибках и записывать их в таблицу для последующего анализа:

BEGIN TRY
    -- Attempt an operation that might fail
    INSERT INTO SalesLT.Customer (CustomerID, FirstName, LastName)
    VALUES (1, 'Test', 'Customer');  -- Duplicate key causes error
END TRY
BEGIN CATCH
    -- Log error details to a table using the ERROR_* functions
    INSERT INTO ErrorLog (
        ErrorTime,
        ErrorNumber,
        ErrorSeverity,
        ErrorState,
        ErrorProcedure,
        ErrorLine,
        ErrorMessage
    )
    VALUES (
        GETDATE(),
        ERROR_NUMBER(),       -- The error number (e.g., 2627 for duplicate key)
        ERROR_SEVERITY(),     -- Severity level (0-25)
        ERROR_STATE(),        -- Error state for debugging
        ISNULL(ERROR_PROCEDURE(), 'Ad hoc query'),  -- NULL if not in a procedure
        ERROR_LINE(),         -- Line number where error occurred
        ERROR_MESSAGE()       -- Full error message text
    );

    -- Re-raise the error to the calling application
    THROW;
END CATCH;

Обработка транзакций с помощью TRY...CATCH

При возникновении ошибок в транзакциях необходимо явно откатить незафиксированную работу. Функция @@TRANCOUNT указывает, активна ли транзакция:

BEGIN TRY
    -- Start a transaction to group multiple operations
    BEGIN TRANSACTION;

    -- First operation: update product prices
    UPDATE SalesLT.Product
    SET ListPrice = ListPrice * 1.05
    WHERE ProductCategoryID = 5;

    -- Second operation: update order totals
    -- If this fails, we want to undo the first update too
    UPDATE SalesLT.SalesOrderHeader
    SET TotalDue = TotalDue * 1.05
    WHERE CustomerID = 12345;

    -- Both operations succeeded, make changes permanent
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Check if a transaction is still active before rolling back
    -- Some errors auto-rollback, so @@TRANCOUNT might be 0
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;  -- Undo all changes from this transaction

    -- Re-raise the error so the caller knows something failed
    THROW;
END CATCH;

Проверка @@TRANCOUNT важна, так как:

• Ошибка может возникнуть раньше BEGIN TRANSACTION
• Некоторые ошибки автоматически откатывают транзакцию перед достижением CATCH
• Попытка отката без активной транзакции приводит к возникновению дополнительной ошибки

Создание пользовательских ошибок с помощью THROW

Инструкция THROW вызывает исключение с пользовательским номером ошибки и сообщением. Используйте его для сигнала об ошибках, характерных для конкретного приложения:

CREATE PROCEDURE ProcessOrder
    @OrderID INT,
    @Quantity INT
AS
BEGIN
    BEGIN TRY
        -- Validate input and raise custom errors for invalid data
        IF @Quantity <= 0
            THROW 50001, 'Quantity must be greater than zero.', 1;

        IF NOT EXISTS (SELECT 1 FROM Orders WHERE OrderID = @OrderID)
            THROW 50002, 'Order not found.', 1;

        -- Process the order
        UPDATE Orders
        SET Quantity = @Quantity
        WHERE OrderID = @OrderID;

    END TRY
    BEGIN CATCH
        -- Log the error before reraising
        EXEC LogError;

        -- THROW without parameters reraises the current error
        THROW;
    END CATCH;
END;

Пользовательские номера ошибок для определяемых пользователем ошибок должны быть 50000 или выше. Параметр состояния (1 в примерах) — это определяемое пользователем значение от 1 до 255, которое может помочь определить, где возникла ошибка.

Использование RAISERROR для отформатированных сообщений

RAISERROR предоставляет больше параметров форматирования, чем THROW, включая подстановку параметров в стиле printf. Включение значений среды выполнения в сообщения об ошибках упрощает отладку, так как вы можете увидеть, какие данные вызвали сбой, не копая журналы или воспроизводив проблему:

DECLARE @ProductName NVARCHAR(100) = 'Widget Pro';
DECLARE @CurrentStock INT = 5;
DECLARE @RequestedQty INT = 10;

IF @CurrentStock < @RequestedQty
BEGIN
    RAISERROR(
        'Insufficient stock for product "%s". Available: %d, Requested: %d',
        16,  -- Severity
        1,   -- State
        @ProductName,
        @CurrentStock,
        @RequestedQty
    );
END;

Внедрение вложенной обработки ошибок

Хранимым процедурам, вызывающим другие процедуры, необходима координированная обработка ошибок. Каждый уровень должен выполнять свою собственную очистку и соответствующим образом распространять ошибки.

CREATE PROCEDURE OuterProcedure
AS
BEGIN
    BEGIN TRY
        -- Outer procedure owns the transaction
        BEGIN TRANSACTION;

        -- First operation in the outer procedure
        UPDATE SomeTable SET Column1 = 'Value';

        -- Call nested procedure - if it fails, error propagates here
        EXEC InnerProcedure;

        -- All operations succeeded, commit the transaction
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Outer procedure handles rollback for all nested calls
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- Propagate error to the application
        THROW;
    END CATCH;
END;
GO

CREATE PROCEDURE InnerProcedure
AS
BEGIN
    BEGIN TRY
        -- Inner procedure does its work within the outer's transaction
        UPDATE AnotherTable SET Column2 = 'Value';
    END TRY
    BEGIN CATCH
        -- Don't rollback here - let the outer procedure handle it
        -- This keeps transaction management in one place
        THROW;  -- Re-raise error to outer procedure
    END CATCH;
END;

Используйте XACT_ABORT для автоматического отката

Вы можете задать XACT_ABORT ON, чтобы SQL Server автоматически откатывал транзакцию при возникновении любой ошибки, даже без TRY...CATCH следующим образом:

SET XACT_ABORT ON;

BEGIN TRANSACTION;
    UPDATE Table1 SET Col1 = 'A';
    UPDATE Table2 SET Col2 = 'B';  -- If this fails, entire transaction rolls back
    UPDATE Table3 SET Col3 = 'C';
COMMIT TRANSACTION;

Объединение XACT_ABORT с TRY...CATCH дает преимущества обоих подходов: XACT_ABORT гарантирует немедленный откат для любой ошибки, в то время как TRY...CATCH позволяет записывать сведения об ошибках и выполнять настраиваемую очистку перед распространением ошибки:

-- XACT_ABORT ON ensures automatic rollback on any error
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    -- Execute multiple procedures as a single unit of work
    EXEC Procedure1;  -- If any of these fail...
    EXEC Procedure2;  -- ...XACT_ABORT automatically rolls back...
    EXEC Procedure3;  -- ...and jumps to the CATCH block

    -- All succeeded, commit the changes
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- With XACT_ABORT ON, the transaction is usually already rolled back
    -- This check handles edge cases where it might still be active
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Log the error details before re-raising
    EXEC LogError;

    -- Let the caller know an error occurred
    THROW;
END CATCH;

Дополнительные сведения об обработке ошибок см. в разделе TRY... CATCH (Transact-SQL) и THROW (Transact-SQL).