1

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.

4
  • I think this is a sybase instruction..
    – McNets
    Nov 26 '16 at 21:36
  • So maybe i'm reading the wrong book? It's a pdf and it's not indicated whether it's a sybase or not
    – andrea
    Nov 27 '16 at 1:10
  • Searching for "set cursor rows" seems to confirm what @mcNets has said.
    – Andriy M
    Nov 27 '16 at 9:59
  • Yes. But is sybase so similar to sqlserver?
    – andrea
    Nov 28 '16 at 21:17
0

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
2
  • Thank you, I will never use cursors anymore... You scared me :))
    – andrea
    Nov 28 '16 at 21:12
  • Ha haa, no problem. Just remember that SQL is primarily a set based language, not a procedural language (such as any programming language). Its strengths are dealing with sets, not dealing with procedural logic. I always suggest people use the right tools for the right jobs. You don't see builders knocking in nails with a screw driver, even though its possible. That is akin to using SQL to loop through records...
    – blobbles
    Nov 28 '16 at 22:37

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.