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

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

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

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;

Collectives™ on Stack Overflow

How to loop through cursor?

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related