如何在 SQL Server 的嵌套过程中处理事务? [英] How to handle Transaction in Nested procedure in SQL server?

问题描述

我有 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 的嵌套过程中处理事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！