While Loops Vs Cursors

[jonwolds]

Hi,

I am currently working on a solution to a problem using Table Variables and While Loops. It is quite a complex solution and, though not experienced, I think it could be done more neatly with a cursor.

In very general terms are there many occasions where it would be better to use a Cursor instead of While Loops?

Thanks
Jon

 

[dbomrrsm]

cursors are very inefficient so I would say - and many more in the forum would back me up on this - avaoid them whenever you can. The only time I would consider using them is on very small sets of data.

[blue]DBomrrsm[/blue]

 

[vongrunt]

Agree in general, though both methods initiate row-based logic.

What people often forget is to declare cursor type - for example, to use static/read_only cursor when possible.

 

[jonwolds]

Cheers Chaps

 

[SQLSister]

In the book Advanced Transact-SQL for SQl Server 2000, (by Ben Gan and Moreau), they state that while loops generally outperform cursors. I haven't actually tried this because I don't use cursors at all. If you have a large data set you might try it both ways and see. But in all honesty the only time I've ever needed to use a cursor was to call system stored procedures for multiple objects.

Questions about posting. See faq183-874

 

[jonwolds]

And Chapesses,