SQL Server 事务处理

Question

我正在运行以下存储过程，但收到错误

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

我在这里错过了什么？

CREATE PROCEDURE spImportData
AS

BEGIN TRANSACTION

BEGIN TRY 
   SET IDENTITY_INSERT PINCDOCControlNew..tblActionType ON
END TRY
BEGIN CATCH
   PRINT 'IDENTITY_INSERT IS ON'
END CATCH
GO

BEGIN TRY
   INSERT INTO PINCDOCControlNew..tblActionType(ActionTypeID,ActionType,ActionTypeDescription)
      SELECT ActionTypeID,ActionType,ActionTypeDescription 
      FROM PINCDOCControlOld..tblActionType

   SET IDENTITY_INSERT PINCDOCControlNew..tblActionType OFF
END TRY
BEGIN CATCH
   SET IDENTITY_INSERT PINCDOCControlNew..tblActionType OFF
   EXECUTE usp_GetErrorInfo
END CATCH

BEGIN TRY
    SET IDENTITY_INSERT PINCDOCControlNew..tblArea ON
END TRY
BEGIN CATCH
    PRINT 'IDENTITY_INSERT IS ON'
END CATCH

GO

BEGIN TRY
    INSERT INTO PINCDOCControlNew..tblArea(AreaID,AreaDescription,AreaNo)
        SELECT AreaNo,AreaDescription,Area 
        FROM PINCDOCControlOld..tblArea

    SET IDENTITY_INSERT PINCDOCControlNew..tblArea OFF
END TRY
BEGIN CATCH
    SET IDENTITY_INSERT PINCDOCControlNew..tblArea OFF
    EXECUTE usp_GetErrorInfo
END CATCH

IF @@ERROR <> 0
BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Error in inserting.', 16, 1)
    RETURN
 END
 COMMIT

Answer 1

我会尝试只有一个 BEGIN TRY .... END TRY block ，其中包含您要执行的所有逻辑。如果出现任何问题 - 在您的逻辑中的任何地方 - 您将被扔进 BEGIN CATCH.... END CATCH block 。

在您的 BEGIN TRY 之前开始您的交易，并且将唯一的 COMMIT 作为您的 TRY block 中的最后一条语句 - 并且在您的 CATCH block ，有一个回滚。

像这样:

CREATE PROCEDURE dbo.spImportData
AS
    BEGIN TRANSACTION
    BEGIN TRY 
        SET IDENTITY_INSERT PINCDOCControlNew..tblActionType ON

        INSERT INTO 
            PINCDOCControlNew..tblActionType(ActionTypeID, ActionType, ActionTypeDescription)
            SELECT 
               ActionTypeID, ActionType, ActionTypeDescription 
            FROM 
              PINCDOCControlOld..tblActionType

        SET IDENTITY_INSERT PINCDOCControlNew..tblActionType OFF

        -- tblArea
        SET IDENTITY_INSERT PINCDOCControlNew..tblArea ON

        INSERT INTO 
             PINCDOCControlNew..tblArea(AreaID, AreaDescription, AreaNo)
           SELECT 
               AreaNo, AreaDescription, Area 
           FROM 
               PINCDOCControlOld..tblArea

        SET IDENTITY_INSERT PINCDOCControlNew..tblArea OFF

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
       ROLLBACK TRANSACTION

       SET IDENTITY_INSERT PINCDOCControlNew..tblActionType OFF
       SET IDENTITY_INSERT PINCDOCControlNew..tblArea OFF

       EXECUTE usp_GetErrorInfo

       RAISERROR ('Error in inserting.', 16, 1)
    END CATCH

使用这种方法，您有恰好一个 BEGIN TRANSACTION，以及一个对应的COMMIT TRANSACTION，或者一个对应的回滚事务

我通常还会将此 SELECT 语句添加到我的 CATCH block 中，以获取错误消息和错误代码:

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