Запись транзакции в t-sql и обработка ошибок

Неужели вы думаете, что есть лучший способ написать транзакцию в t-sql? Есть ли лучший подход, который улучшает ремонтопригодность и производительность приложения, использующего эту транзакцию?

-- Description: Insert email Receiver under specified subject
-- =============================================
ALTER PROCEDURE [Contact].[Receiver_stpInsert]
    @First_Name nvarchar(30),
    @Last_Name nvarchar(30),
    @Email varchar(60),
    @Subject_Id int
AS
BEGIN   
    SET NOCOUNT ON;

    DECLARE @error_num int;


    BEGIN TRANSACTION 

    INSERT INTO Contact.Receiver(First_Name, Last_Name, Email) VALUES(@First_Name, @Last_Name, @Email); 

    SET @error_num = @@ERROR;
    IF (@error_num <> 0)
        BEGIN
            ROLLBACK;
            RETURN;
        END

    DECLARE @rec_record_id int;
    SET @rec_record_id = (SELECT Record_Id FROM Contact.Receiver WHERE Email = @Email);

    SET @error_num = @@ERROR;
    IF (@error_num <> 0)
        BEGIN
            ROLLBACK;
            RETURN;
        END

    INSERT INTO Contact.Receiver_Subject(Receiver_Id, Subject_Id) VALUES(@rec_record_id, @Subject_Id);

    SET @error_num = @@ERROR;
    IF (@error_num <> 0)
        BEGIN
            ROLLBACK;
            RETURN;
        END

    SET @error_num = @@ERROR;
    IF (@error_num <> 0)
        BEGIN
            ROLLBACK;
            RETURN;
        END
    ELSE
        BEGIN   
            Commit;

        END

END

Ответы

Ответ 1

Если вы используете SQL 2005 или более позднюю версию, вы можете использовать блок TRY... CATCH, например:

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO Contact.Receiver(First_Name, Last_Name, Email) VALUES (@First_Name, @Last_Name, @Email); 
    ... other inserts etc 
    ...
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

Таким образом, вы не повторяете те же блоки проверки кода @@ERROR. Если вы хотите узнать, какая ошибка произошла, в блоке BEGIN CATCH вы можете получить различные биты информации:

ERROR_NUMBER() возвращает номер ошибки.

ERROR_SEVERITY() возвращает серьезность.

ERROR_STATE() возвращает номер состояния ошибки.

ERROR_PROCEDURE() возвращает имя хранимой процедуры или триггера где произошла ошибка.

ERROR_LINE() возвращает номер строки внутри процедуры, которая вызвала ошибка.

ERROR_MESSAGE() возвращает полный текст сообщения об ошибке. Текст включает значения, предоставленные для любых подставляемые параметры, такие как длины, имена объектов или время.

Ответ 2

В течение долгого времени я выступал за использование TRY/CATCH и вложенные транзакции в хранимых процедурах.

Этот шаблон дает вам не только очень упрощенную обработку ошибок блока TRY/CATCH по сравнению с проверкой @@ERROR, но также дает вложенную семантику для вызова процедур или без ничего.

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

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
        return;
    end catch   
end

Отступами этого подхода являются:

не работает с распределенными транзакциями. Поскольку точки сохранения транзакций несовместимы с распределенными транзакциями, вы не можете использовать этот шаблон, когда требуются распределенные транзакции. Распространенные транзакции IMHO являются злыми и никогда не должны использоваться в любом случае.
изменяет исходную ошибку. Эта проблема присуща блокам TRY/CATCH, и вы ничего не можете с этим поделать. Приложение, которое подготовлено для обработки исходных кодов ошибок SQL Server (например, 1202, 1205, 2627 и т.д.), Должно быть изменено для обработки кодов ошибок в вышеуказанном диапазоне 50000, поднятых кодом Transact-SQL, который использует TRY/CATCH.

Также следует предупредить об использовании SET XACT_ABORT ON. Этот параметр заставит пакет прерывать транзакцию при любой ошибке. Это вызывает любую транзакцию транзакций TRY/CATCH, в основном бесполезную, и я рекомендую избегать.

Ответ 3

Если у вас есть SQL Server 2000 или раньше, тогда да - проверка значения @@ERROR - это в основном все, что вы можете сделать.

В SQL Server 2005 Microsoft представила конструкцию TRY... CATCH, которая делает ее намного проще:

BEGIN TRY
  ......
  -- your T-SQL code here
  ......
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

    -- do other steps, if you want
END CATCH

Ответ 4

Спрошено не так давно. Мой ответ с шаблоном TRY/CATCH

Ответ 5

Если вы используете sql 2005 или выше, вы должны рассмотреть подход TRY CATCH

Ответ 6

Вы можете обернуть все это в попытку catch, а затем вам нужно только скопировать откат в одном месте. Подробнее см. .