Reputation: 16340
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
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
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