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.

Solution 1

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.

Solution 2

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.

Solution 3

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 ;)

Solution 4

you could use the snippet below to kick a sp after soem condition are rised. I assume that you ahev some sort of CurrentJobStatus table where all the jobs/sp keeps their status...

-- *** reload data on N Support.usp_OverrideMode with checks on Status
/* run 
Support.usp_OverrideMode.Number1.sql
and
Support.usp_OverrideMode.Number2.sql
*/


DECLARE @FileNameSet TABLE (FileName VARCHAR(255));

INSERT INTO @FileNameSet
VALUES ('%SomeID1%');

INSERT INTO @FileNameSet
VALUES ('%SomeID2%');

DECLARE @BatchRunID INT;

DECLARE @CounterSuccess INT = 0;
DECLARE @CounterError INT = 0;

-- Loop
WHILE WHILE (@CounterError = 0 AND  @CounterSuccess < (select COUNT(1) c from @FileNameSet) )
BEGIN

DECLARE @CurrenstStatus VARCHAR(255)
SELECT @CurrenstStatus = CAST(GETDATE() AS VARCHAR)


    -- Logic goes here: The loop can be broken with the BREAK command.
    SELECT @CounterSuccess = COUNT(1)
    FROM dbo.CurrentJobStatus t
    INNER JOIN @FileNameSet fns
        ON (t.FileName LIKE fns.FileName) 
    WHERE LoadStatus = 'Completed Successfully'

    SELECT @CounterError = COUNT(1)
    FROM dbo.CurrentJobStatus t
    INNER JOIN @FileNameSet fns
        ON (t.FileName LIKE fns.FileName) 
    WHERE LoadStatus = 'Completed with Error(s)'

    -- Throttle the loop for 3 seconds.    
    WAITFOR DELAY '00:00:03';

    select @CurrenstStatus = @CurrenstStatus +char(9)+ '@CounterSuccess ' + CAST(@CounterSuccess AS VARCHAR(11)) 
 +  char(9)+ 'CounterError ' + CAST(@CounterError AS VARCHAR(11)) 

    RAISERROR (
            'Looping... @ %s'
            ,0
            ,1
            ,@CurrenstStatus
            )
    WITH NOWAIT;

END
-- TODO add some codition on @CounterError value
        /* run 
Support.usp_OverrideMode.WhenAllSuceed.sql
*/

Note the code is flexibile you can add as many condition checks on the @FileNameSet table var Mario

View more solutions

Comments

[Marcus Adams]

I agree with WoLpH. Why can't you put the break condition in the WHILE statement? That is the better way. It's normal to setup the condition, have the WHILE statement, and setup the condition again in the loop. Don't be afraid of a few duplicate lines of code. Though, I've written a few WHILE(TRUE) statements too.

[alpav]

@Marcus: Duplicate lines of code is what I always try to avoid. I believe infinite loop is lesser evil than duplicate lines of code.

[David Bridge]

Might be worth noting that you cannot use WAITFOR in a function.