Code inside SQL Cursor will only execute first FETCH

Question

I have a SQL Cursor which I'm having issues with. When I remove the IF @debug = 1 statement from inside the cursor, only the first record from the FETCH will get updated but if I leave the IF @debug = 1 all the required records are updated. Any idea as to why this is happening, I know most likely something is wrong with my Cursor? Code is below:

DECLARE Verify_Shipment_Cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT DISTINCT lpd_shipment_id, lpd_po_number, lpd_customer_id, lpd_sku, lpd_lottable01, lpd_lottable02, lpd_lottable03, lpd_putaway_zone, lpd_pdt
        FROM PO_DETAIL01(NOLOCK) 
    WHERE lpd_shipment_id = @i_SHIPMENT_ID 
            AND lpd_po_number = @i_POKEY 
            AND lpd_customer_id = @i_CUSTOMER_ID
            AND lpd_status = @AvailableStatus

OPEN Verify_Shipment_Cur
    WHILE @ShipmentSKUCount >= @ShipmentSKUCountCur
        BEGIN
            FETCH NEXT FROM Verify_Shipment_Cur INTO @ShipmentID, @POKey, @CustomerID, @SKU, @Lottable01, @Lottable02, @Lottable03, @PutawayZone, @PDT

            IF EXISTS(SELECT 1 FROM PO_DETAIL(NOLOCK) WHERE pd_asn_number = @i_SHIPMENT_ID AND pd_po_number = @i_POKEY 
                        AND pd_sku = @SKU AND pd_type = @ShmtType AND pd_ordered_qty <> pd_received_qty)
                BEGIN
                    UPDATE PO_DETAIL
                        SET pd_adjusted_qty = pd_ordered_qty - pd_received_qty
                    WHERE pd_asn_number = @i_SHIPMENT_ID 
                        AND pd_po_number = @i_POKEY 
                        AND pd_sku = @SKU 
                        AND pd_type = @ShmtType
                END

            UPDATE PO_DETAIL
                SET pd_lottable01 = @Lottable01
                    , pd_lottable02 = @Lottable02
                    , pd_lottable03 = @Lottable03
                    , pd_lottable04 = ''
                    , pd_lottable05 = @Date
                    , pd_putaway_zone = @PutawayZone
                    , pd_pdt = @PDT
                    , pd_status = @VerifiedStatus
            WHERE pd_asn_number = @i_SHIPMENT_ID 
                        AND pd_po_number = @i_POKEY 
                        AND pd_sku = @SKU 
                        AND pd_type = @ShmtType


            UPDATE PO_DETAIL01
                SET lpd_status = @VerifiedStatus
            WHERE lpd_shipment_id = @i_SHIPMENT_ID 
                    AND lpd_po_number = @i_POKEY 
                    AND lpd_customer_id = @i_CUSTOMER_ID
                    AND lpd_status = @AvailableStatus

        IF @debug = 1
            BEGIN
                SELECT @ShipmentSKUCount AS SKUCOUNT
                , @ShipmentSKUCountCur AS SKUCOUNTCUR
                , @SKU AS SKU
                , @ShipmentID AS SHIPMENT
                , @POKey AS POKEY
            END

            SET @ShipmentSKUCountCur = @ShipmentSKUCountCur + 1

        END
CLOSE Verify_Shipment_Cur
DEALLOCATE Verify_Shipment_Cur

Rob White · Accepted Answer · 2013-08-30 15:58:14Z

1

It looked ok to me but i obviously dont have your data to assist. Can I recommend putting a few print statements in various parts of your cursor. That way you can see how the code is actually flowing. It doesnt help but thats what I would do.

answered Aug 30, 2013 at 15:58

Rob White

1,0121 gold badge8 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jorame Over a year ago

I did what you recommended and the data flow looks good. No issues that I could see.

notricky · Accepted Answer · 2013-08-30 17:09:53Z

0

Please, can you try to define explicilty both variables: set @ShipmentSKUCount=[initial value],set @ShipmentSKUCountCur=[initial or constant value] and see what will happen?
Also, I found there is no checking for a @@FETCH_STATUS. It may also result into reading same row twice or more.
Please, give a feedback.

answered Aug 30, 2013 at 17:09

notricky

1791 gold badge2 silver badges18 bronze badges

Collectives™ on Stack Overflow

Code inside SQL Cursor will only execute first FETCH

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related