How to organize infinite while loop in SQL Server ?

Question

I want to use infinite WHILE loop in SQL Server 2005 and use BREAK keyword to exit from it on certain condition.

while true does not work, so I have to use while 1=1. Is there a better way to organize infinite loop ?

I know that I can use goto, but while 1=1 begin .. end looks better structurally.

Answer 1

Using While 1 = 1 with a Break statement is the way to do it. There is no constant in T-SQL for TRUE or FALSE.

Answer 2

If you really have to use an infinite loop than using while 1=1 is the way I'd do it.

The question here is, isn't there some other way to avoid an infinite loop? These things just tend to go wrong ;)

Answer 3

In addition to the WHILE 1 = 1 as the other answers suggest, I often add a "timeout" to my SQL "infintie" loops, as in the following example:

DECLARE @startTime datetime2(0) = GETDATE();

-- This will loop until BREAK is called, or until a timeout of 45 seconds.
WHILE (GETDATE() < DATEADD(SECOND, 45, @startTime))
BEGIN
    -- Logic goes here: The loop can be broken with the BREAK command.

    -- Throttle the loop for 2 seconds.    
    WAITFOR DELAY '00:00:02';
END

I found the above technique useful within a stored procedure that gets called from a long polling AJAX backend. Having the loop on the database-side frees the application from having to constantly hit the database to check for fresh data.