How to avoid duplicated FETCH in T-SQL when using a cursor?
There's a good structure posted online by Chris Oldwood which does it quite elegantly:
DECLARE @done bit = 0 WHILE (@done = 0) BEGIN -- Get the next author. FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lname IF (@@FETCH_STATUS <> 0) BEGIN SET @done = 1 CONTINUE END -- -- stuff done here with inner cursor elided -- END
This is what I've resorted to (oh the shame of it):
WHILE (1=1)BEGIN FETCH NEXT FROM C1 INTO @foo, @bar, @bufar, @fubar, @bah, @fu, @foobar, @another, @column, @in, @the, @long, @list, @of, @variables, @used, @to, @retrieve, @all, @values, @for, @conversion IF (@@FETCH_STATUS <> 0) BEGIN BREAK END -- Use the variables hereENDCLOSE C1DEALLOCATE C1
You can see why I posted a question. I don't like how the control of flow is hidden in an if
statement when it should be in the while
.
The first Fetch
shouldn't be a Fetch next
, just a fetch
.
Then you're not repeating yourself.
I'd spend more effort getting rid of the cursor, and less on DRY dogma, (but if it really matters, you could use a GOTO
:) - Sorry, M. Dijkstra)
GOTO DryWHILE @@FETCH_STATUS = 0 BEGIN --- stuff hereDry: FETCH NEXT FROM Employee_Cursor; END;