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

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.

Answer 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.

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

Answer 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