How to loop through cursor?

Question

I dont know why this query returns only the first row infinitely

    DECLARE db_cursor1 CURSOR FOR select ilduedt,accountno,ilno from TBPAYSCHED where accountno ='000520285344' and ilno!=0 order by id
    DECLARE @ilduedt datetime;
    DECLARE @accountno varchar(MAX);
    DECLARE @ilno int;
    DECLARE @tempdate datetime;
    OPEN db_cursor1;
    FETCH NEXT FROM db_cursor1 INTO @ilduedt,@accountno,@ilno
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
    select @ilduedt,@accountno,@ilno
    END;
    CLOSE db_cursor1;
    DEALLOCATE db_cursor1;

tbpaysched

accountno    | ilno | ilduedt
------------------------------------------
000520285344    0     2017-07-30 00:00:00.000
000520285344    1     2017-09-15 00:00:00.000
000520285344    2     2017-08-30 00:00:00.000
000520285344    3     2017-09-15 00:00:00.000

It only returns the row with ilno 1 infinitely. I dont know but I think I'm missing something stupid.

The best answer to your question is that you should toss this cursor away and rewrite your process with set based code. — Sean Lange
– Sean Lange, Commented Sep 13, 2017 at 13:11

LONG · Accepted Answer · 2017-09-13 12:28:32Z

1

You missed anther FETCH NEXT in the while loop, should be :

WHILE @@FETCH_STATUS = 0  
BEGIN  
select @ilduedt,@accountno,@ilno
FETCH NEXT FROM db_cursor1 INTO @ilduedt,@accountno,@ilno    
END

answered Sep 13, 2017 at 12:28

LONG

4,6302 gold badges21 silver badges41 bronze badges

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

Comments

Ross Bush · Accepted Answer · 2017-09-13 12:28:50Z

1

You are not retrieving the next row from the record set with a call to FETCH_NEXT. However, that code above, assuming there is one row in the record set, should result in an infinite loop.

    FETCH NEXT FROM db_cursor1 INTO @ilduedt,@accountno,@ilno
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        select @ilduedt,@accountno,@ilno
        FETCH NEXT FROM db_cursor1 INTO @ilduedt,@accountno,@ilno
    END;

answered Sep 13, 2017 at 12:28

Ross Bush

15.2k2 gold badges39 silver badges65 bronze badges

Collectives™ on Stack Overflow

How to loop through cursor?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related