2

Using RedGate's SQL Prompt & loving it. But I used the "curff" snippet for the first time today ("Fast-forward read-only cursor"), and it produced the following TSQL (which I've filled in to make it a concrete example):

DECLARE @variable INT
DECLARE Curse CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT ID FROM #TempTable

OPEN Curse
FETCH NEXT FROM Curse INTO @variable

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM Curse INTO @variable  --This seems wrong...

    PRINT ('Hello, I am # ' + CAST(@variable AS VARCHAR(10)))
END

CLOSE Curse
DEALLOCATE Curse

Isn't the location of the inner FETCH NEXT (inside the WHILE loop) incorrect? It should be at the end of the loop, or at least after the query (queries) that need to use the variable, no?

1 Answer 1

1

YES, it is incorrect. The inner FETCH NEXT should go at the END of the loop. The code above would result in the following:

Hello, I am # 2
Hello, I am # 3
Hello, I am # 4
Hello, I am # 5
Hello, I am # 5

Correcting the problem, and putting the FETCH NEXT at the end of the loop, results in the correct & expected output:

Hello, I am # 1
Hello, I am # 2
Hello, I am # 3
Hello, I am # 4
Hello, I am # 5

Now to go submit this to RedGate...

3
  • Sample code at sqlfiddle.com/#!6/32258/1
    – NateJ
    Commented May 18, 2016 at 21:21
  • Apparently it's old-ish news -- forums.red-gate.com/… .. but I'm hopeful that they'll fix it and/or this post will help people catch the mistake.
    – NateJ
    Commented May 18, 2016 at 21:32
  • And in less than 24 hours they've put out a fix! Love how responsive they are. :)
    – NateJ
    Commented May 19, 2016 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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