WHILE
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