SQL Server ПОПЫТАТЬСЯ ПОХВАТИТЬ

В этом руководстве вы узнаете, как использовать конструкцию SQL Server TRY CATCH для обработки исключений в хранимых процедурах.

Обзор SQL Server TRY CATCH

Конструкция TRY CATCH позволяет изящно обрабатывать исключения в SQL Server. Чтобы использовать конструкцию TRY CATCH, сначала поместите группу операторов Transact-SQL, которые могут вызвать исключение, в блок BEGIN TRY…END TRY следующим образом:

BEGIN TRY  
   -- statements that may cause exceptions
END TRY  

Затем вы используете блок BEGIN CATCH…END CATCH сразу после блока TRY:

BEGIN CATCH  
   -- statements that handle exception
END CATCH  

Ниже представлена полная конструкция TRY CATCH:

BEGIN TRY  
   -- statements that may cause exceptions
END TRY 
BEGIN CATCH  
   -- statements that handle exception
END CATCH  

Если операторы между блоками TRY завершаются без ошибок, операторы между блоками CATCH не будут выполнены. Однако, если какой-либо оператор внутри блока TRY вызывает исключение, управление передается операторам в блоке CATCH.

Функции блока CATCH

Внутри блока CATCH вы можете использовать следующие функции для получения подробной информации о произошедшей ошибке:

• ERROR_LINE() возвращает номер строки, в которой произошло исключение.
• ERROR_MESSAGE() возвращает полный текст сгенерированного сообщения об ошибке.
• ERROR_PROCEDURE() возвращает имя хранимой процедуры или триггера, где произошла ошибка.
• ERROR_NUMBER() возвращает номер произошедшей ошибки.
• ERROR_SEVERITY() возвращает уровень серьезности произошедшей ошибки.
• ERROR_STATE() возвращает номер состояния произошедшей ошибки.

Обратите внимание, что вы используете эти функции только в блоке CATCH. Если вы используете их вне блока CATCH, все эти функции вернут NULL.

Вложенные конструкции TRY CATCH

Вы можете вложить конструкцию TRY CATCH внутрь другой конструкции TRY CATCH. Однако, как блок TRY, так и блок CATCH могут содержать вложенную конструкцию TRY CATCH, например:

BEGIN TRY
    --- statements that may cause exceptions
END TRY
BEGIN CATCH
    -- statements to handle exception
    BEGIN TRY
        --- nested TRY block
    END TRY
    BEGIN CATCH
        --- nested CATCH block
    END CATCH
END CATCH

Примеры SQL Server TRY CATCH

Сначала создайте хранимую процедуру с именем usp_divide, которая делит два числа:

CREATE PROC usp_divide(
    @a decimal,
    @b decimal,
    @c decimal output
) AS
BEGIN
    BEGIN TRY
        SET @c = @a / @b;
    END TRY
    BEGIN CATCH
        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;  
    END CATCH
END;
GO

В этой хранимой процедуре мы поместили формулу в блок TRY и вызвали функции блока CATCH ERROR_* внутри блока CATCH.

Во-вторых, вызовите хранимую процедуру usp_divide для деления 10 на 2:

DECLARE @r decimal;
EXEC usp_divide 10, 2, @r output;
PRINT @r;

Вот результат

5

Поскольку в блоке TRY не произошло исключения, хранимая процедура завершилась в блоке TRY.

В-третьих, попытайтесь разделить 20 на ноль, вызвав хранимую процедуру usp_divide:

DECLARE @r2 decimal;
EXEC usp_divide 10, 0, @r2 output;
PRINT @r2;

На следующем рисунке показан результат:

Из-за ошибки деления на ноль, вызванной формулой, управление было передано оператору внутри блока CATCH, который вернул подробную информацию об ошибке.

SQL Serer TRY CATCH с транзакциями

Внутри блока CATCH вы можете проверить состояние транзакций с помощью функции XACT_STATE().

• Если функция XACT_STATE() возвращает -1, это означает, что ожидается нефиксируемая транзакция, следует выполнить оператор ROLLBACK TRANSACTION.
• Если функция XACT_STATE() возвращает 1, это означает, что ожидается фиксируемая транзакция. В этом случае вы можете выполнить оператор COMMIT TRANSACTION.
• Если функция XACT_STATE() возвращает 0, это означает, что транзакция не находится в состоянии ожидания, поэтому вам не нужно предпринимать никаких действий.

Хорошей практикой является проверка состояния транзакции перед выдачей оператора COMMIT TRANSACTION или ROLLBACK TRANSACTION в блоке CATCH, чтобы обеспечить согласованность.

Пример использования TRY CATCH с транзакциями

Сначала создадим две новые таблицы sales.persons и sales.deals для демонстрации:

CREATE TABLE sales.persons
(
    person_id  INT
    PRIMARY KEY IDENTITY, 
    first_name NVARCHAR(100) NOT NULL, 
    last_name  NVARCHAR(100) NOT NULL
);
CREATE TABLE sales.deals
(
    deal_id   INT
    PRIMARY KEY IDENTITY, 
    person_id INT NOT NULL, 
    deal_note NVARCHAR(100), 
    FOREIGN KEY(person_id) REFERENCES sales.persons(
    person_id)
);
insert into 
    sales.persons(first_name, last_name)
values
   ('John','Doe'),
   ('Jane','Doe');
insert into 
    sales.deals(person_id, deal_note)
values
   (1,'Deal for John Doe');

Затем создайте новую хранимую процедуру с именем usp_report_error, которая будет использоваться в блоке CATCH для сообщения подробной информации об ошибке:

CREATE PROC usp_report_error
AS
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_MESSAGE() AS ErrorMessage;  
GO

Затем разработайте новую хранимую процедуру, которая удаляет строку из таблицы sales.persons:

CREATE PROC usp_delete_person(
    @person_id INT
) AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        -- delete the person
        DELETE FROM sales.persons 
        WHERE person_id = @person_id;
        -- if DELETE succeeds, commit the transaction
        COMMIT TRANSACTION;  
    END TRY
    BEGIN CATCH
        -- report exception
        EXEC usp_report_error;
        -- Test if the transaction is uncommittable.  
        IF(XACT_STATE()) = -1  
        BEGIN  
            PRINT  N'The transaction is in an uncommittable state.' +  
                    'Rolling back transaction.'  
            ROLLBACK TRANSACTION;  
        END;  
        -- Test if the transaction is committable.  
        IF(XACT_STATE()) = 1  
        BEGIN  
            PRINT N'The transaction is committable.' +  
                'Committing transaction.'  
            COMMIT TRANSACTION;     
        END;  
    END CATCH
END;
GO

В этой хранимой процедуре мы использовали функцию XACT_STATE() для проверки состояния транзакции перед выполнением COMMIT TRANSACTION или ROLLBACK TRANSACTION внутри блока CATCH.

После этого вызовите хранимую процедуру usp_delete_person для удаления персоны с идентификатором 2:

EXEC usp_delete_person 2;

Никаких исключений не произошло.

Наконец, вызовите хранимую процедуру usp_delete_person для удаления человека с идентификатором 1:

EXEC usp_delete_person 1;

Произошла следующая ошибка:

В этом руководстве вы узнали, как использовать конструкцию SQL Server TRY CATCH для обработки исключений в хранимых процедурах.