When to use a While loop

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;
SET ROWCOUNT 1000;
WHILE @RowCount>0
BEGIN 
    BEGIN TRAN;
    DELETE FROM Table WHERE somedate<'20160101'    
    SET @RowCount = @@ROWCOUNT;
    COMMIT TRAN;
    CHECKPOINT;
END

Select allOpen 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

@Olaf

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:

Microsoft:

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.

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.