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

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...

  • Sample code at sqlfiddle.com/#!6/32258/1 – NateJ May 18 '16 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 May 18 '16 at 21:32
  • And in less than 24 hours they've put out a fix! Love how responsive they are. :) – NateJ May 19 '16 at 20:24

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.