WHILE (T-SQL)

Sets a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.

Syntax

WHILE Boolean_expression
    {sql_statement | statement_block}
    [BREAK]
    {sql_statement | statement_block}
    [CONTINUE]

Arguments

Boolean_expression

Is an expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.

{sql_statement | statement_block}

Is any Transact-SQL statement or statement grouping as defined with a statement block. To define a statement block, use the control-of-flow keywords BEGIN and END.

BREAK

Causes an exit from the innermost WHILE loop. Any statements appearing after the END keyword, marking the end of the loop, are executed.

CONTINUE

Causes the WHILE loop to restart, ignoring any statements after the CONTINUE keyword.

Remarks

If two or more WHILE loops are nested, the inner BREAK exits to the next outermost loop. First, all the statements after the end of the inner loop run, and then the next outermost loop restarts.

Examples

A. Use BREAK and CONTINUE with nested IF...ELSE and WHILE

In this example, if the average price is less than $30, the WHILE loop doubles the prices and then selects the maximum price. If the maximum price is less than or equal to $50, the WHILE loop restarts and doubles the prices again. This loop continues doubling the prices until the maximum price is greater than $50, and then exits the WHILE loop and prints a message.

USE pubs

GO

WHILE (SELECT AVG(price) FROM titles) < $30

BEGIN

    UPDATE titles

        SET price = price * 2

    SELECT MAX(price) FROM titles

    IF (SELECT MAX(price) FROM titles) > $50

        BREAK

    ELSE

        CONTINUE

END

PRINT 'Too much for the market to bear'

B. Using WHILE within a procedure with cursors

The following WHILE construct is a section of a procedure named count_all_rows. For this example, this WHILE construct tests the return value of @@FETCH_STATUS, a function used with cursors. Because @@FETCH_STATUS may return -2, -1, or 0, all three cases must be tested. If a row is deleted from the cursor results since the time this stored procedure was executed, that row is skipped. A successful fetch (0) causes the SELECT within the BEGIN...END loop to execute.

USE pubs

DECLARE tnames_cursor CURSOR

FOR

    SELECT TABLE_NAME

    FROM INFORMATION_SCHEMA.TABLES

OPEN tnames_cursor

DECLARE @tablename sysname

--SET @tablename = 'authors'

FETCH NEXT FROM tnames_cursor INTO @tablename

WHILE (@@FETCH_STATUS <> -1)

BEGIN

    IF (@@FETCH_STATUS <> -2)

    BEGIN    

        SELECT @tablename = RTRIM(@tablename)

        EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '

                + @tablename )

        PRINT ' '

    END

    FETCH NEXT FROM tnames_cursor INTO @tablename

END

CLOSE tnames_cursor

DEALLOCATE tnames_cursor

See Also