When to use a While loop

Adam Davis
Adam Davis used Ask the Experts™

Can someone please explain to me when I would need to use an while loop. I understand the principles of the while loop but not sure when I would actually need to use one?

Thank you
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
T-SQL is a set-based programming language and is (editorializing here) optimized to perform data operations on entire sets of data.

Loops are when you need to create custom logic to execute on a single row, where you can't pull it off with a single UPDATE-INSERT-DELETE-Whatever statement.  This is nicknamed RBAR, or 'row by agonizing row' as it takes much longer to process then a single statement returning all rows.

For more details, you'll have to spell out your situation...
Máté FarkasDatabase Developer and Administrator
For example when a cycle is has no defined iterations (like FOR cycle) but it sould iterate until a condition.
WHILE (@HasMoreData = 1)
      -- do something

Open in new window

So you have to do something WHILE the value of variable @HasMoreData is 1.
Technical Architect, Capgemini India
A WHILE loop in T-SQL in internally implemented as a cursor, i.e. the only time on needs to use a WHILE loop is when the code needs to process one record at a time and some non T-SQL component is involved or when the dynamic SQL is complex that it cannot be generated via a set-based approach.

Here's an excellent piece by Aaron Bertrand which demonstrates that a WHILE loop is indeed a CURSOR: http://sqlblog.com/blogs/aaron_bertrand/archive/2012/01/26/the-fallacy-that-a-while-loop-isn-t-a-cursor.aspx

As a general rule, cursors (or any cursor-like operation) will have a considerably high performance overhead when compared to a set-based query trying to achieve the same result.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Olaf DoschkeSoftware Developer
Most often use should be with cursors


Open in new window

Since you don't know when you fetch the last row, this detects it.

More general speaking, the use of loops and other program flow control statements is needed, once you do more than just sql queries but a little more program logic around that. For example composing sql for dynamic sql execution. Most of the time it's easier to do such things on the level of application business logic outside of SQL Server. Not only but mainly because the T-SQL language is quite poor to do other things than queries.

Bye, Olaf.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
I understand the principles of the while loop but not sure when I would actually need to use one?
Whenever you need to interact with records, one by one. As stated above this is usually used when working with cursors but this is not a static rule so it depends only on your needs and imagination :)
Olaf DoschkeSoftware Developer

Vitor is right.

An even simpler answer: When you google "T-SQL WHILE" you'll not only find the definition, you'll find code of people using it. You can see for what they use it. ;)

I just did that and found no other uses than for cursor on the first few result pages. I'll give another one here related to a recent question. The following batch processing of a DELETE can lower the stress on the transaction log:

Declare @RowCount int;
Set @RowCount=1;
WHILE @RowCount>0
    DELETE FROM Table WHERE somedate<'20160101'    
    SET @RowCount = @@ROWCOUNT;

Open in new window

Especially if the DELETE would otherwise delete millions of rows or causes cascading deletes, the processing of the DELETE command unchanged but limited by SET ROWCOUNT in a WHILE loop can help to have an overall better performance and preventing growth of the transaction log without needing to write a WHERE clause with changing range of dates or IDs.

Bye, Olaf.


Thank you all for your help

That technique with SET ROWCOUNT 1000 is something new to me and it is awesome!. Thanks for sharing !
oops, I was a bit too soon enthusiastic:


Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax. For more information, see TOP (Transact-SQL).

So it stopped with SQL 2008.
Olaf DoschkeSoftware Developer

Well, that's new to me, thank YOU for pointing that out.

Bye, Olaf.

Edit: While the deprecation already was announced in the SQL2008 topic, the version really deprecating this feature is the future version following SQL2016, see https://msdn.microsoft.com/en-us/library/ms143729.aspx

Edit 2: I actually tried with the Microsoft SQL Server 2016 (RC3) - 13.0.1400.361 (X64) and SET ROWCOUNT still is in effect on DELETE.
As the list says it only will deprecate in the next version. Anyway it's easy to do the same with a TOP (1000) clause.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial