問(wèn)題描述

我有 2 個(gè) proc，即 Proc1 和 Proc2.我在 proc2 中執(zhí)行 proc1.兩個(gè)程序中都有多個(gè) DML 操作.proc1 的輸出在 proc2 中用于 DML 操作.如果在 proc2 中發(fā)生錯(cuò)誤，則如何在兩個(gè) proc 中處理事務(wù)以回滾所有 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?

我應(yīng)該在兩個(gè) proc 中都寫事務(wù)嗎?

Should I write transaction in both proc?

推薦答案

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

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;

(在主庫(kù)中編譯并標(biāo)記為系統(tǒng)程序)

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

我們使用這個(gè)錯(cuò)誤處理程序如下.在演示中，我有一個(gè)外部 proc 和一個(gè)內(nèi)部 proc 都使用事務(wù).

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

編譯并運(yùn)行后:

EXEC dbo.uspOuterProc

你應(yīng)該得到這個(gè)結(jié)果:

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.

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