sql - 在WHILE LOOP中插入记录时发生奇怪的TRY CATCH行为

Question

下面是我正在运行的代码(准备复制粘贴到SQL Server中)。我实质上是在尝试使用WHILE LOOP将一个表中的记录一张一张地插入到新表中，并使用第三张表记录每次插入的结果。一旦我可以解决此问题，此代码将被调整并在几个存储过程中使用。

这是代码:

SET NOCOUNT ON;

BEGIN TRY
    BEGIN TRANSACTION

    --Create dummy tables

    DROP TABLE IF EXISTS #OldTable
    DROP TABLE IF EXISTS #NewTable
    DROP TABLE IF EXISTS #LoggingTable

    CREATE TABLE #OldTable (
        ID int IDENTITY(1,1) NOT NULL PRIMARY KEY
        ,OldValue varchar(64)
    )

    INSERT INTO #OldTable
    VALUES
        ('1')
        ,('2')
        ,('3')
        ,('Four')
        ,('Five')
        ,('6')
        ,('Seven')

    CREATE TABLE #NewTable (
        ID int IDENTITY(1,1) NOT NULL PRIMARY KEY
        ,NewValue int
    )

    CREATE TABLE #LoggingTable (
        ID int IDENTITY(1,1) NOT NULL PRIMARY KEY
        ,OldTableID int NULL
        ,NewTableID int NULL
        ,InsertStatus varchar(MAX)
    )

    --Begin insert loop

    DECLARE @currentID int = NULL

    DECLARE THIS_CURSOR CURSOR FAST_FORWARD FOR
        SELECT ID FROM #OldTable ORDER BY ID

    OPEN THIS_CURSOR
    FETCH NEXT FROM THIS_CURSOR INTO @currentID

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            --Perform insert
            INSERT INTO #NewTable
            OUTPUT @currentID, INSERTED.ID, 'Insert successful' INTO #LoggingTable (OldTableID, NewTableID, InsertStatus)
            SELECT CAST(OldValue AS int) FROM #OldTable WHERE ID = @currentID

            FETCH NEXT FROM THIS_CURSOR INTO @currentID
        END TRY
        BEGIN CATCH
            INSERT INTO #LoggingTable (OldTableID, NewTableID, InsertStatus) VALUES (@currentID, NULL, 'Error occurred during insert operation')
            FETCH NEXT FROM THIS_CURSOR INTO @currentID
        END CATCH   
    END

    CLOSE THIS_CURSOR
    DEALLOCATE THIS_CURSOR

    SELECT * FROM #OldTable
    SELECT * FROM #NewTable
    SELECT * FROM #LoggingTable

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    PRINT 'An error has occurred';

    -- Test if the transaction is uncommittable.  
    IF XACT_STATE() = -1  
    BEGIN  
        PRINT 'The transaction is in an uncommittable state. Rolling back transaction.'; 
        ROLLBACK TRANSACTION;  
    END;

    -- Test if the transaction is committable.  
    IF XACT_STATE() = 1  
    BEGIN  
        PRINT 'The transaction is committable. Committing transaction.';
        COMMIT TRANSACTION;     
    END;

    THROW;
END CATCH

这是我收到的错误消息:

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

令人讨厌的行似乎是这样的:

INSERT INTO #LoggingTable (OldTableID, NewTableID, InsertStatus) VALUES (@currentID, NULL, 'Error occurred during insert operation')

但是，如果我注释掉这一行(我仍然需要!)，它只会移到代码中的另一行，这通常意味着还有其他事情在进行。我已经在网上进行了彻底的搜索，但仍不确定导致此错误的原因是什么。

关于此错误的最奇怪的部分是，如果您删除外部的 TRY CATCH和 TRANSACTION，脚本将完全按预期运行，没有任何问题；但是，这不是一个可行的恕我直言的解决方案，因为一旦将其包含在存储过程中，就需要适当的错误捕获和事务处理。

Answer 1

我将“COMMIT TRANSACTION”移动了几行，并且运行良好(抛出异常是因为它试图在回滚事务之前试图写入catch块中的表):

SET NOCOUNT ON;

BEGIN TRY
    SET XACT_ABORT ON;
    BEGIN TRANSACTION;

    --Create dummy tables
    DROP TABLE IF EXISTS #OldTable;
    DROP TABLE IF EXISTS #NewTable;
    DROP TABLE IF EXISTS #LoggingTable;

    CREATE TABLE #OldTable
    (
        ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
        OldValue VARCHAR(64)
    );

    INSERT INTO #OldTable
    VALUES
    ('1'),
    ('2'),
    ('3'),
    ('Four'),
    ('Five'),
    ('6'),
    ('Seven');

    CREATE TABLE #NewTable
    (
        ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
        NewValue INT
    );

    CREATE TABLE #LoggingTable
    (
        ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
        OldTableID INT NULL,
        NewTableID INT NULL,
        InsertStatus VARCHAR(MAX)
    );

    --Begin insert loop

    DECLARE @currentID INT = NULL;

    DECLARE THIS_CURSOR CURSOR FAST_FORWARD FOR
    SELECT ID
    FROM #OldTable
    ORDER BY ID;

    OPEN THIS_CURSOR;
    FETCH NEXT FROM THIS_CURSOR
    INTO @currentID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            --Perform insert
            INSERT INTO #NewTable
            OUTPUT @currentID,
                   INSERTED.ID,
                   'Insert successful'
            INTO #LoggingTable
            (
                OldTableID,
                NewTableID,
                InsertStatus
            )
            SELECT CAST(OldValue AS INT)
            FROM #OldTable
            WHERE ID = @currentID;

            FETCH NEXT FROM THIS_CURSOR
            INTO @currentID;
            COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            INSERT INTO #LoggingTable
            (
                OldTableID,
                NewTableID,
                InsertStatus
            )
            VALUES
            (@currentID, NULL, 'Error occurred during insert operation');
            FETCH NEXT FROM THIS_CURSOR
            INTO @currentID;
        END CATCH;
    END;

    CLOSE THIS_CURSOR;
    DEALLOCATE THIS_CURSOR;

    SELECT *
    FROM #OldTable;
    SELECT *
    FROM #NewTable;
    SELECT *
    FROM #LoggingTable;


    SET XACT_ABORT OFF;
END TRY
BEGIN CATCH
    PRINT 'An error has occurred';

    -- Test if the transaction is uncommittable.  
    IF XACT_STATE() = -1
    BEGIN
        PRINT 'The transaction is in an uncommittable state. Rolling back transaction.';
        ROLLBACK TRANSACTION;
    END;

    -- Test if the transaction is committable.  
    IF XACT_STATE() = 1
    BEGIN
        PRINT 'The transaction is committable. Committing transaction.';
        COMMIT TRANSACTION;
    END;

    THROW;
END CATCH;