Set cursor rows command

Question

I'm new to sqlserver and i'm learning about t-sql cursor.

I've seen in a book called "t-sql user guide" an example of cursor declaration.

There is a command to return more rows for each fetch:

set cursor rows 3 for authors_crsr

I've tried it in my sqlserver2012, but it did not work. I tried searching in google but had not luck.

What's wrong with this command? Maybe this command has been deleted from the version i'm using? (2012)

Thank you.

Answer 1

This command is not a SQL server command, as others have commented. However, if you did need to fetch 3 lines into 3 different sets of variables, you could do so using:

FETCH ABSOLUTE 1 FROM cursor_name INTO @Variable1;

FETCH ABSOLUTE 3 FROM cursor_name INTO @Variable3;

FETCH ABSOLUTE 2 FROM cursor_name INTO @Variable2;

FETCH ABSOLUTE 1 FROM cursor_name INTO @Variable1;

Remember that each FETCH always resets the current row to the position of the cursor. This requires that you declare your cursor like: DECLARE cursor_name SCROLL CURSOR FOR .... Careful here though, this requires your SQL statement for the cursor declaration to have an ORDER BY to ensure you receive the cursor in an expected order, if that is important.

Lastly - try your hardest to NOT use cursors! Especially those that require scrolling. They are evil by nature, have black souls and each time you use one Edgar Codd turns over in his grave. OK, maybe not that bad, but you get what I mean :-)

EDIT:

Actually a better implementation would be to use a variable instead of the number after the ABSOLUTE key word, so your loop would look like:

WHILE @@FETCH_STATUS = 0  
BEGIN
    FETCH ABSOLUTE @Ctr FROM contact_cursor INTO @var1;

    SELECT @Ctr = @Ctr + 2
    FETCH ABSOLUTE @Ctr FROM contact_cursor INTO @var2;

    SELECT @Ctr = @Ctr - 1
    FETCH ABSOLUTE @Ctr FROM contact_cursor INTO @var3;

    SELECT @Ctr = @Ctr - 1
    FETCH ABSOLUTE @Ctr FROM contact_cursor INTO @var1;

    --Increment the counter
    SELECT @Ctr = @Ctr + 1
END