Why my cursor isn't returning the first row values?

Question

I have a cursor in order to separate rows based on a required quantity for each item, this works kind of fine, with the exception that it doesn't return the first rows and instead, if the row for example has a value of quantity 3, it adds it to the last row. My code is the following:

DECLARE curCURSOR CURSOR 
FOR 
SELECT ITEM, CITY, OPEN_QUANTITY, MODEL, PROMISE , SALES_ORDER , LINE_NUMBER, DESCRIPTION, CARRIER FROM T_tmpShipReporCommAssy
WHERE [DESCRIPTION]  like 'COM%ASSY%' AND CITY = @pSite 
AND FG_AVAILABLE > 0
ORDER BY  PROMISE, SALES_ORDER, LINE_NUMBER

OPEN curCURSOR
FETCH curCURSOR INTO @ITEM, @CITY, @required_quantity, @MODEL, @PROMISE, @SALES_ORDER, @LINE_NUMBER, @DESCRIPTION, @CARRIER
    IF EXISTS(SELECT 1 FROM AvailToShipPalletsSN)
    BEGIN
        DELETE FROM AvailToShipPalletsSN
        DBCC CHECKIDENT ('[AvailToShipPalletsSN]', RESEED, 0);
    END
DECLARE @e INT
SET @e = 0

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH FROM curCURSOR INTO @ITEM, @CITY, @required_quantity, @MODEL, @PROMISE, @SALES_ORDER, @LINE_NUMBER, @DESCRIPTION, @CARRIER
    WHILE (@e < @required_quantity)
    BEGIN
        INSERT INTO AvailToShipPalletsSN (item, city, required_quantity, MODEL, PROMISE, SALES_ORDER, LINE_NUMBER, DESCRIPTION, CARRIER) values
            (@ITEM, @CITY, @required_quantity, @MODEL, @PROMISE, @SALES_ORDER, @LINE_NUMBER, @DESCRIPTION, @CARRIER)
        SET @e = @e + 1
    END
    SET @e = 0
END

CLOSE curCURSOR
DEALLOCATE curCURSOR

Now, when I run a select count to the original table and the generated one:

       SELECT MODEL, SUM(OPEN_QUANTITY) FROM T_tmpShipReporCommAssy
       WHERE [DESCRIPTION]  like 'COM%ASSY%' AND CITY = @psite
       AND FG_AVAILABLE > 0
       AND PROMISE <= @pdate
       GROUP BY MODEL

       SELECT model, COUNT(required_quantity) FROM AvailToShipPalletsSN 
       group by model

It returns me the following results:

(Table with correct quantities)

CMF200M 10

CMF010M 2

CMF200A 1

H200F 2

(Table generated by my cursor)

CMF200M 8

CMF010M 2

CMF200A 1

H200F 4

I don't really know how to fix this behavior, I'd appreciate a little help on this topic

Answer 1

The problem is that you fetch a row just after you open the cursor, but don't do anything with the fetched values. So you end up missing the first row. To correct that, reorganize your code this way:

-- first delete the other table
if exists(select ....)
begin
  delete ...
end

-- then, declare variables, open the cursor, etc.
declare curCURSOR cursor for ...
declare @e int = 0

open curCURSOR

-- then start fetching. use the following pattern:
fetch from curCURSOR into @...
while @@FETCH_STATUS = 0
begin
  -- do your thing:
  while @e < @required_quantity
    ...
  -- then, after you did your thing
  -- and just before the end of the while @@fetch_status block,
  -- fetch again
  fetch from curCURSOR into @...
end

-- finally close, deallocate, etc.
close ...
deallocate ...

That takes care of missing the first row.

The reason of your second problem might be that you added a condition to the query that isn't in the cursor: PROMISE <= @pdate.

That is, there might be some rows with promise after @pdate that got inserted into the second table, but don't show when you query the first table with that condition.