On what criteria can While loops and Cursors be compared?

Question

Suppose that I have a query that really must be either a cursor or a while loop. What criteria can be used to judge which should be used?

Notably not a duplicate of this question. I'm not trying to ask what the pros/cons of each option is. I'm assuming that I already know that and I'm asking how to come to a decision between the two options when stuck.

Answer 1

A lot of is comes down to personal preference or whether the other developers you work with have a visceral reaction to seeing a cursor. Personally, the only time I use a cursor is when it can replace having to declare a temporary table with a RID on it, insert rows into it, then do a WHILE loop till @RID <= @MaxRID

And even then, the main reason I like them is it's easier to write a code snippet to loop using a cursor than it is to stub out the table.

So basically, if I have a scenario where I would have to do something like the following to use a WHILE loop...

-- Table based approach
declare @loopTable table
(
    RID int identity(1,1),
    TableName nvarchar(128)
)

declare 
    @RID int,
    @MaxRID int,
    @TableName nvarchar(128)

insert into @LoopTable(TableName)
select name
from sys.tables

select 
    @RID = 1,
    @MaxRID = @@rowcount

while @RID <= @MaxRID
begin

    select @TableName = TableName
    from @loopTable
    where RID = @RID
    select 'Do something for ' + @TableName

    select @RID += 1
end

I might replace it with...

-- Cursor based approach
declare @TableName nvarchar(128)
declare c cursor local fast_forward for
    select name
    from sys.tables
open c

fetch next from c into @TableName

while @@fetch_status = 0
begin

    select 'Do something for ' + @TableName
    
    fetch next from c into @TableName

end

deallocate c

That's about it. But really, don't underestimate the amount of revulsion many database developers have to cursors. Their logic is clunky, it's sort of hard to understand why you need a local fast_forward cursor for them to be acceptable, and there are so many more ways to abuse cursors than to use them properly, that most people I know just steer clear of them.

If you're in doubt, I would reach for a WHILE loop instead as, even in the best of circumstances, it will perform the same function as a local fast_forward cursor, and be immediately recognizable to other database developers. Unless of course you're working with a crew of people who like cursors better in which case, there's your answer.