問題描述

我有 2 個 proc，即 Proc1 和 Proc2.我在 proc2 中執行 proc1.兩個程序中都有多個 DML 操作.proc1 的輸出在 proc2 中用于 DML 操作.如果在 proc2 中發生錯誤，則如何在兩個 proc 中處理事務以回滾所有 DML 操作?

I have 2 proc i.e. Proc1 and Proc2. I am executing proc1 inside proc2. There are multiple DML operation in both procedure. output of proc1 is used in proc2 for DML operation. if Error occurred in proc2 then how to handle transaction in both proc for rollback all DML operation?

我應該在兩個 proc 中都寫事務嗎?

Should I write transaction in both proc?

推薦答案

我們使用基于 http://www.sommarskog.se/error_handling/Part1.html 我們 - 在適用時 - 包括在我們的(嵌套)交易中以確保鏈得到正確管理:

We use a generic error handler procedure based on http://www.sommarskog.se/error_handling/Part1.html that we - when applicable - include in our (nested) transactions to ensure the chain is managed properly:

CREATE PROCEDURE [dbo].[sp_ErrorHandler](@caller VARCHAR(255))
AS BEGIN
    SET NOCOUNT ON;
    DECLARE @errmsg NVARCHAR(2048), @severity TINYINT, @state TINYINT, @errno INT, @lineno INT;
    SELECT @errmsg=REPLACE(ERROR_MESSAGE(), 'DatabaseException: ', 'DatabaseException: '+QUOTENAME(@caller)+' --> ')
         , @severity=ERROR_SEVERITY()
         , @state=ERROR_STATE()
         , @errno=ERROR_NUMBER()
         , @lineno=ERROR_LINE();
    IF @errmsg NOT LIKE 'DatabaseException%' BEGIN
        SELECT @errmsg=N'DatabaseException: '+QUOTENAME(@caller)+N', Line '+LTRIM(STR(@lineno))+N', Error '+LTRIM(STR(@errno))+N': '+@errmsg;
    END;
    RAISERROR('%s', @severity, @state, @errmsg);
END;

(在主庫中編譯并標記為系統程序)

(Compiled in the master database and marked as system procedure)

我們使用這個錯誤處理程序如下.在演示中，我有一個外部 proc 和一個內部 proc 都使用事務.

We use this error handler as follows. In the demo I have an outer proc and an inner proc both using a transaction.

CREATE PROCEDURE dbo.uspOuterProc
AS
  BEGIN
  SET NOCOUNT, XACT_ABORT ON;

  BEGIN TRY
  BEGIN TRANSACTION;

  EXEC dbo.uspInnerProc;

  PRINT 1;

  COMMIT;
  END TRY
  BEGIN CATCH
  IF @@trancount > 0
  ROLLBACK TRANSACTION;

  EXEC master.dbo.sp_ErrorHandler @caller = 'dbo.uspOuterProc';
  END CATCH;
  END;
GO

CREATE PROCEDURE dbo.uspInnerProc
AS
  BEGIN
  SET NOCOUNT, XACT_ABORT ON;

  BEGIN TRY
  BEGIN TRANSACTION;

  PRINT 2;

  SELECT 1 / 0;

  PRINT 3;

  COMMIT;
  END TRY
  BEGIN CATCH
  IF @@trancount > 0
  ROLLBACK TRANSACTION;

  EXEC master.dbo.sp_ErrorHandler @caller = 'dbo.uspInnerProc';
  END CATCH;
  END;
GO

編譯并運行后:

EXEC dbo.uspOuterProc

你應該得到這個結果:

2

Msg 50000, Level 16, State 1, Procedure sp_ErrorHandler, Line 13 [Batch Start Line 48]
DatabaseException: [dbo.uspOuterProc] --> [dbo.uspInnerProc], Line 12, Error 8134: Divide by zero error encountered.

這篇關于如何在 SQL Server 的嵌套過程中處理事務?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！