CODEWITHSUNDEEP
sqlsql-servercursor
user2220670
user2220670

Reputation: 125

cursor skips every other row

I have a cursor that works but skips every other record. My fetch next looks like this

OPEN DemandCur
While 1 = 1
BEGIN

    FETCH NEXT FROM DemandCur INTO
    @----,
    @+++++

    select @index = (select demand from TechCoDemand where Date = '2014-11-30') 

    IF @index <= 0 BREAK;   

    IF @@FETCH_STATUS <> 0 BREAK;

    FETCH NEXT FROM DemandCur INTO 
    @---,
    @++++
End

Close DemandCur
Deallocate DemandCur

I changed it to

FETCH NEXT

And it stopped skipping records but I get an error message after the query is done:

Msg 16916, Level 16, State 1, Line 121
A cursor with the name 'NEXT' does not exist.

Upvotes: 0

Views: 1739

Answers (2)

Ben Thul
Ben Thul

Reputation: 32687

As others have alluded to in the comments above, you're fetching twice within your while loop. While (ha!) you do need to "prime the pump" (so to speak) by fetching one row outside of the while loop first, here's an idiom that I like to use to avoid that entirely.

declare cursor foobar for
select ...

open foobar

while(1=1)
begin

    fetch next from foobar into ...
    if (@@fetch_status <> 0)
       break

    --process results

end

close foobar
deallocate foobar

This has the benefit of only having to have one fetch statement to maintain but, more germane to this conversation, avoids your error entirely.

Upvotes: 0

Spock
Spock

Reputation: 4900

Change your code to this....

OPEN DemandCur
FETCH NEXT FROM DemandCur INTO @----, @+++++
While @@FETCH_STATUS = 0
BEGIN
    select @index = (select demand from TechCoDemand where Date = '2014-11-30') 
    IF @index <= 0 BREAK;   
    FETCH NEXT FROM DemandCur INTO @---, @++++
END

Close DemandCur
Deallocate DemandCur

Upvotes: 1

Related Questions