Transactions within Cursor

  • I want to use a cursor to select all the orders within a session and put these records away an order at a time if an order header or detail fails rollback and process the next order or commit and process the next record. I have come up with the following stored procedure but I am new to using transactions in this manner and wondering if this is a good idea doing it this way. Any help will be greatly appreciated.

    CREATE PROCEDURE dbo.sp_PutawayRecords(@SyncSessionGuid as uniqueidentifier) as

    DECLARE @intErrorCode int

    --Load Address records and comments

    EXEC @intErrorCode = sp_LoadAddressFromStaging

    --If the address records have been loaded without error process orders

    IF @intErrorCode = 0

    BEGIN

    DECLARE @OrderGuid uniqueidentifier

    -- Create cursor for processing orders

    DECLARE curOrders CURSOR FOR

    SELECT orders_id AS guid

    FROM orders

    WHERE session_guid = @SyncSessionGuid

    --Open Cursor

    OPEN curOrders

    FETCH NEXT FROM curOrders INTO @OrderGuid

    WHILE @@FETCH_STATUS = 0

    BEGIN

    BEGIN TRAN Orders

    --Process header records

    EXEC @intErrorCode = sp_LoadOrdersFromStaging @SyncsessionGuid,@OrderGuid

    --Process detail records

    EXEC @intErrorCode = sp_LoadOrderedItemsFromStaging @SyncsessionGuid,@OrderGuid

    --If both are successful Comit else rollback and process any existing orders

    IF @intErrorCode = 0

    BEGIN

    COMMIT TRAN Orders

    END

    ELSE

    BEGIN

    ROLLBACK TRAN Orders

    END

    FETCH NEXT FROM curOrders INTO @OrderGuid

    END

    -- clean Up

    CLOSE curOrders

    DEALLOCATE curOrders

    END

  • Looks like you have the just of it. But I don't know what kind of errors your expecting, I think certain errors will cause the transaction to roll back, and the stored procedure to fail prematurely. Like anything Test it.

    To me using a cursor is not a terribly good idea. Have you looked at a non cursor solution, or are you just trying this out to learn?

  • In the sp I call if the insert/updates fail for the order header or detail I return 1 and want to rollback that particular order. How could I avoid using a cursor in this case?

  • Wrong order>>>

    1 - Validate with the application that the order is valid.

    2 - (assuming batch operation). Revalidate that everything is fine.

    3 - Execute the tasks without error.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply