sql - 具有多个更新的SQL Server事务，插入

Question

因此，我们有多个存储的proc在一个早晨的SQL作业中使用，并且这些proc被依次调用。如果proc内部的一个查询失败，我们将为每个查询执行错误捕获/记录操作，以便我们确切地知道失败的部分。但是问题在于，如果某些操作失败，其中某些过程将很难重新开始，因此我正在考虑在每个存储的proc中实现TRANSACTION。

当前过程与此类似：

CREATE PROCEDURE [dbo].[spStep01]
( 
    @Return_Message     Varchar(1024)   OUT     -- Error messages returned to the calling program
)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @ErrorCode              int
DECLARE @ApplicationNumber      int             
DECLARE @TaskNumber             int             
DECLARE @TaskCompleted          smallint        
DECLARE @TaskFailed             smallint        
DECLARE @TaskRunning            smallint        
DECLARE @ErrorSeverity          smallint        
DECLARE @ErrorState             smallint        

SELECT @ErrorCode = @@ERROR
SELECT @ApplicationNumber   = 10                
SELECT @TaskNumber          = 1
SELECT @TaskCompleted       = 0
SELECT @TaskFailed          = -1
SELECT @TaskRunning         = 1
SELECT @ErrorSeverity       = 16
SELECT @ErrorState          = 1

/***************************************************************************
*  first insert
***************************************************************************/
    BEGIN TRY   
        INSERT INTO ...
    END TRY
    BEGIN CATCH
        SELECT @Return_Message = 'FAILED - first insert did not populate'
        EXEC dbo.spTrackTask '', @ApplicationNumber, @TaskNumber, @TaskFailed, @Return_Message      
        RAISERROR (@Return_Message, @ErrorSeverity, @ErrorState)
        RETURN 
    END CATCH

/***************************************************************************
*  second insert
***************************************************************************/
    BEGIN TRY   
        INSERT INTO ...
    END TRY
    BEGIN CATCH
        SELECT @Return_Message = 'FAILED -  second insert did not populate'
        EXEC dbo.spTrackTask '', @ApplicationNumber, @TaskNumber, @TaskFailed, @Return_Message      
        RAISERROR (@Return_Message, @ErrorSeverity, @ErrorState)
        RETURN 
    END CATCH


/***************************************************************************
* Procedure has completed successfully
***************************************************************************/
    SELECT @Return_Message = 'SUCCESS - Inserts were complete'
    EXEC dbo.spTrackTask '', @ApplicationNumber, @TaskNumber, @TaskCompleted, @Return_Message   


/*************************************
*  Get the Error Message for @@Error
*************************************/
    IF @ErrorCode <> 0
    BEGIN
        SELECT  @Return_Message = [Description]     -- Return the SQL Server error
          FROM  master.dbo.SYSMESSAGES
         WHERE  error = @ErrorCode
    END

/*************************************
*  Return from the Stored Procedure
*************************************/
    RETURN @ErrorCode                               -- =0 if success,  <>0 if failure

END

我要确定的是是否将所有 TRY/CATCH块包装在 TRANSACTION中，并且如果它将回滚所有内容，则会引发错误。我四处查看，发现一个 TRY/CATCH块中有几个 examples，但大多数存储过程中都有多个。我在交易方面经验不足，因此在这种情况下我不确定100％如何正确实施。

将其包装在 TRANSACTION中会起作用吗？还是有更好的方法来做到这一点？

Answer 1

这个怎么样

BEGIN TRANSACTION tx
BEGIN TRY
  @CurrentStep = "First Insert"
  INSERT ...

  @CurrentStep = "Second Insert"
  INSERT ...Second

  IF @@TRANCOUNT > 0
  BEGIN --SUCCESS, nothing failed, now I can commit!!
    SELECT @Return_Message = 'SUCCESS - Inserts were complete'
    EXEC dbo.spTrackTask '', @ApplicationNumber, @TaskNumber, @TaskCompleted, @Return_Message   
    COMMIT TRANSACTION tx;    -- now everything is committed
  END
END TRY

BEGIN CATCH 
  IF @@TRANCOUNT > 0 --something failed
  BEGIN
    IF @CurrentStep = 'First Insert'
      SELECT @Return_Message = 'FAILED - first insert did not populate'
    ELSE IF @CurrentStep = 'Second Insert'
      SELECT @Return_Message = 'FAILED - second insert did not populate'

    EXEC dbo.spTrackTask '', @ApplicationNumber, @TaskNumber, @TaskFailed, @Return_Message      
    RAISERROR (@Return_Message, @ErrorSeverity, @ErrorState)

    ROLLBACK TRAN tx; -- everything is rolled back
  END
END CATCH

希望这对您有帮助，请记住，事务中的所有内容都应在一个位置提交或回滚，否则最好进行多个事务。