How to avoid duplicated FETCH in T-SQL when using a cursor?

Question

In T-SQL, when iterating results from a cursor, it seems to be common practice to repeat the FETCH statement before the WHILE loop. The below example from Microsoft:

DECLARE Employee_Cursor CURSOR FOR
SELECT EmployeeID, Title FROM AdventureWorks2012.HumanResources.Employee
    WHERE JobTitle = 'Marketing Specialist';
OPEN Employee_Cursor;

FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS = 0
    BEGIN
        FETCH NEXT FROM Employee_Cursor;
    END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO

(Notice how FETCH NEXT FROM Employee_Cursor; appears twice.)

If the FETCH selects into a long list of variables, then we have a large duplicated statement which is both ugly and of course, "non-DRY" code.

I'm not aware of a post-condition control-of-flow T-SQL statement so it seems I'd have to resort to a WHILE(TRUE) and then BREAK when @@FETCH_STATUS is not zero. This feels clunky to me.

What other options do I have?

In code you shown, replace first FETCH NEXT FROM Employee_Cursor with GOTO Employee_Cursor_Fetch and place label Employee_Cursor_Fetch: immediately before remaining FETCH NEXT. You can notice that label name is derived from cursor name (Employee_Cursor) to save you from some thinking about label names. — miroxlav
– miroxlav, Commented Jun 26, 2016 at 21:05

Vince Bowdren · Accepted Answer · 2013-09-26 14:01:23Z

16

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

answered Sep 26, 2013 at 14:01

Vince Bowdren

9,2853 gold badges37 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bernhard Hofmann · Accepted Answer · 2012-09-04 10:53:31Z

12

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 here
END

CLOSE C1
DEALLOCATE 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.

answered Sep 4, 2012 at 10:53

Bernhard Hofmann

10.5k12 gold badges65 silver badges80 bronze badges

1 Comment

Jeroen Over a year ago

I think, technically, this should be the accepted answer, as it does avoid duplicating the FETCH statement.

podiluska · Accepted Answer · 2012-09-04 10:44:59Z

4

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 Dry
WHILE @@FETCH_STATUS = 0 
BEGIN 
    --- stuff here

Dry:
    FETCH NEXT FROM Employee_Cursor; 
END;

edited Sep 4, 2012 at 10:44

answered Sep 4, 2012 at 10:31

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

2 Comments

Bernhard Hofmann Over a year ago

I totally agree with the stance on cursors. Sadly, I must allow some rows to fail without failing the entire update. I had my insert-into-select all done and then I had to resort to cursors to ignore failing conversions in my select. Ho hum.

podiluska Over a year ago

@BernhardHofmann Can you not pre-emptively catch the failures with a WHERE clause?

Tamir Gerber · Accepted Answer · 2020-10-14 21:15:28Z

Here is my humble contribution. Single FETCH statement, no GOTO, no BREAK, no CONTINUE.

-- Sample table
DROP TABLE IF EXISTS #tblEmployee;
CREATE TABLE #tblEmployee(ID int, Title varchar(100));
INSERT INTO #tblEmployee VALUES (1, 'First One'), (2, 'Second Two'), (3, 'Third Three'), (3, '4th Four');
-- Cursor with one FETCH statement
DECLARE @bEOF bit=0, @sTitle varchar(200), @nID int;
DECLARE cur CURSOR LOCAL FOR SELECT ID, Title FROM #tblEmployee;
OPEN cur;
WHILE @bEOF=0
BEGIN
    FETCH NEXT FROM cur INTO @nID, @sTitle;
    IF @@FETCH_STATUS<>0
        SET @bEOF=1;
    ELSE
    BEGIN
        PRINT Str(@nID)+'. '+@sTitle;
    END;
END;
CLOSE cur;
DEALLOCATE cur;
-- Cleanup
DROP TABLE IF EXISTS #tblEmployee;

Anuj Balan · Accepted Answer · 2012-09-04 13:39:29Z

-2

It is obvious that a cursor is the pointer to the current row in the recordset. But mere pointing isn't gonna make sense unless it can be used. Here comes the Fetch statement into the scene. This takes data from the recordset, stores it in the variable(s) provided. so if you remove the first fetch statement the while loop won't work as there is not "FETCHED" record for manipulation, if you remove the last fetch statement, the "while" will not loop-through.

So it is necessary to have both the fetch statement to loop-through the complete recordset.

edited Sep 4, 2012 at 13:39

Anuj Balan

7,74123 gold badges62 silver badges94 bronze badges

answered Sep 4, 2012 at 11:16

Jitendra Gupta

8441 gold badge10 silver badges16 bronze badges

1 Comment

Bernhard Hofmann Over a year ago

It's not necessary (see my answer above). I was asking for better ways to avoid writing the control of flow, not whether I need to have the fetch twice.

Fedor Hajdu · Accepted Answer · 2012-09-04 10:34:14Z

-5

Simply said you can't... that's just how most where statements in SQL work. You need to get the first line before the loop and then do it again in the while statement.

The better question how to get rid of the cursor and try to solve your query without it.

answered Sep 4, 2012 at 10:34

Fedor Hajdu

4,7053 gold badges35 silver badges50 bronze badges

3 Comments

Vince Bowdren Over a year ago

Strongly disagree. You can avoid the duplication, though removing the cursor is a worthy aim too.

Nick Painter Over a year ago

While I would agree that in most cases cursors should be avoided in favor of set based operations, there are some instances where a cursor is absolutely necessary. Ie. Where rows need to be evaluated on a line by line basis and the result of previously read rows will determine the result of subsequent reads.

SvendK Over a year ago

I know I'm late to the game, but this is first on my Google search - why is this the accepted answer? It's simply not correct, as shown multiple times already (although after this one was selected as the answer). Please select the best of the other answers :-) I have used Bernhard's solution, but Vince's is also quite OK. I can't let myself use GOTOs of sheer terror of getting mocked by other developers (even though break/continue actually is the same thing) :D

Collectives™ on Stack Overflow

How to avoid duplicated FETCH in T-SQL when using a cursor?

6 Answers 6

Comments

1 Comment

2 Comments

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

2 Comments

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related