CODEWITHSUNDEEP
sqlsql-server
Ivan-Mark Debono
Ivan-Mark Debono

Reputation: 16340

Transaction within a cursor

Is the following the correct way to use transactions within a cursor:

SET CURSOR_CLOSE_ON_COMMIT ON;

DECLARE cur CURSOR LOCAL FOR 
    SELECT * FROM @ordersToProcess;
OPEN cur;

DECLARE @OrderId int;

FETCH NEXT FROM cur INTO @OrderId;
WHILE @@FETCH_STATUS = 0 BEGIN
    BEGIN TRY
        BEGIN TRAN;
        EXEC process_order @OrderId;
        COMMIT TRAN;
        DEALLOCATE cur;                 
        SET CURSOR_CLOSE_ON_COMMIT OFF;
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN;
        DEALLOCATE cur;
        SET CURSOR_CLOSE_ON_COMMIT OFF;
        THROW;
    END CATCH;  

    FETCH NEXT FROM cur INTO @OrderId;
END;

Upvotes: 0

Views: 9618

Answers (2)

cloudsafe
cloudsafe

Reputation: 2506

Deallocate and Close after the cursor has finished:

DECLARE cur CURSOR LOCAL FOR 
    SELECT * FROM @ordersToProcess;
OPEN cur;
DECLARE @OrderId int;

FETCH NEXT FROM cur INTO @OrderId;
WHILE @@FETCH_STATUS = 0 BEGIN
    BEGIN TRY
        BEGIN TRAN;
        EXEC process_order @OrderId;
        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN;
        THROW;
    END CATCH;  

    FETCH NEXT FROM cur INTO @OrderId;
END;

BEGIN TRY      
    CLOSE Cursor1
    DEALLOCATE Cursor1                                  
END TRY
BEGIN CATCH
 --Do nothing
END CATCH

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

No. You have this code:

WHILE @@FETCH_STATUS = 0 BEGIN
    BEGIN TRY
        BEGIN TRAN;
        EXEC process_order @OrderId;
        COMMIT TRAN;
        DEALLOCATE cur;                 
        SET CURSOR_CLOSE_ON_COMMIT OFF;
    END TRY
    . . .

This runs one time through the loop, deallocates the cursor and then . . . well, you have a problem on the second time through the loop.

I think you intend to dealloc after the while loop.

Upvotes: 3

Related Questions