1

I have a cursor declaration:

DECLARE idCursor CURSOR         
    FOR SELECT DISTINCT [id], [data]
        FROM #tempRemaining  
OPEN idCursor

And the statement:

SELECT DISTINCT [id], [data]
        FROM #tempRemaining  

Over which the cursor operates, returns:

id   data  
1    a  
2    c  

However when I FETCH NEXT FROM idCursor INTO @id, @data, I get:

id   data  
2    c  

Which is the second result in the SELECT result. Why is that? And how can I obtain the first row?
My entire code looks like this:

DECLARE idCursor CURSOR                     --iterates over IDs present in the data set
    LOCAL FORWARD_ONLY FAST_FORWARD         --optimising for speed and memory
    FOR SELECT DISTINCT [id], [data]
        FROM #tempRemaining

SELECT DISTINCT [id], [data]
        FROM #tempRemaining

DECLARE @data char(1),
        @id int

OPEN idCursor
FETCH NEXT FROM idCursor
    INTO @id, @data

WHILE @@FETCH_STATUS=0
BEGIN
    PRINT CAST(@id as char(1)) + ' ' + @data
    FETCH NEXT FROM idCursor
END
CLOSE idCursor
DEALLOCATE idCursor 

And the result is just the second row, and a blank row.

3

You are looking at the wrong output. The output you are looking at is the results pane. If you look in the messages pane you will see that you are in fact printing the first row twice. In both cases it's because of your second fetch

FETCH NEXT FROM idCursor

You have to tell it the variables you want to put the data into. Change it to look like the first FETCH (like this)

FETCH NEXT FROM idCursor
INTO @id, @data

As soon as you add that part the results pane will stop showing up and in your messages pane you will get the correct results.

  • Not dumb. Little mistakes like that keep developers up at night (trust me from personal experience) – Kenneth Fisher Jul 27 '15 at 15:34
2

There are two possible explanations:

  1. Your code looks like this:

    DECLARE c CURSOR FOR ...
    OPEN c;
    FETCH c INTO @var; -- fetches 1st row
    WHILE @@FETCH_STATUS = 0
    BEGIN
      FETCH c INTO @var; -- fetches 2nd row
      -- do something
    END
    
  2. If that's not the case, then since you don't have an ORDER BY, SQL Server is free to return the results in whatever order it deems most efficient (and how it iterates over the cursor is not guaranteed to run the same way that the query returns on its own). By not adding ORDER BY, you're telling SQL Server you don't care about or expect a certain order (see #3 here). To fix this, add an ORDER BY to the query you feed into the cursor.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.