Alex_404 Alex_404 - 1 year ago 70
SQL Question

Adding additional cycle to my WHILE loop

I have a cursor that goes through a list of table+column names (ordered by table and column name) and produces SQL queries. Part of a query is constructed on each cycle and the whole query is executed when the table name is different from previous cycle's table name (which is saved in a variable).

The problem with this approach is that last row does not trigger the execution part, as there is no new table name after the last row. I have to repeat the whole code for the logic after the loop just to handle the last row and execute last query.

Loop condition is the usual:

WHILE @@FETCH_STATUS = 0 ... <logic> ... <fetch next> END

I have tried to change it to:

WHILE 1=1 ... <logic> ... IF @@FETCH_STATUS = -1 BREAK ... <fetch next> END

which does execute the last query and exits the loop, but the problem with this is that the loop would trigger on empty set, which is bad.

Is there a way to avoid repeating the loop code after the loop in my case? It is a lot of code and hard to maintain.

PS The cursor is necessary, the code is unreadable without it.

Answer Source

I think the logic you're looking for is:

Declare YourCursor Cursor ...

While (1 = 1)
    Fetch Next From YourCursor Into @C_YourVariables    

    If @@Fetch_Status <> 0 Break

    <Do stuff>

Close YourCursor
Deallocate YourCursor
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download