while and break...continue

while sets a condition for the repeated execution of a statement or statement block. The statements are executed repeatedly as long as the specified condition is true.

The syntax is:

while boolean_expression 
     statement

In this example, the select and update statements are repeated, as long as the average price remains less than $30:

while (select avg(price) from titles) < $30 
begin 
   select title_id, price 
   from titles 
   where price > $20 
   update titles 
   set price = price * 2 
end
(0 rows affected) 
 
title_id    price 
------      ------- 
PC1035      22.95 
PS1372      21.59 
TC3218      20.95 
 
(3 rows affected) 
(18 rows affected) 
(0 rows affected) 
title_id    price 
------      ------- 
BU1032      39.98 
BU1111      23.90 
BU7832      39.98 
MC2222      39.98 
PC1035      45.90 
PC8888      40.00 
PS1372      43.18 
PS2091      21.90 
PS3333      39.98 
TC3218      41.90 
TC4203      23.90 
TC7777      29.98 
 
(12 rows affected) 
(18 rows affected) 
(0 rows affected)

break and continue control the operation of the statements inside a while loop. break causes an exit from the while loop. Any statements that appear after the end keyword that marks the end of the loop are executed. continue causes the while loop to restart, skipping any statements after continue but inside the loop. break and continue are often activated by an if test.

The syntax for break...continue is:

while boolean expression
     begin 
          statement
          [statement]...
          break 
          [statement]...
          continue
          [statement]...
     end

Here is an example using while, break, continue, and if that reverses the inflation caused by the previous examples. As long as the average price remains more than $20, all prices are reduced by half. The maximum price is then selected. If it is less than $40, the while loop exits; otherwise, it attempts to loop again. continue allows print to execute only when the average is more than $20. After the while loop ends, a message and a list of the highest priced books print.

while (select avg(price) from titles) > $20 
begin 
    update titles 
        set price = price / 2 
    if (select max(price) from titles) < $40 
        break 
    else
        if (select avg(price) from titles) < $20
            continue 
    print "Average price still over $20"
end
 
select title_id, price from titles
    where price > $20
 
print "Not Too Expensive"
(18 rows affected) 
(0 rows affected)
(0 rows affected)
Average price still over $20
(0 rows affected) 
(18 rows affected) 
(0 rows affected)
 
title_id    price  
--------    -------
PC1035        22.95  
PS1372        21.59  
TC3218        20.95  
 
(3 rows affected) 
Not Too Expensive

If two or more while loops are nested, break exits to the next outermost loop. First, all the statements after the end of the inner loop execute. Then, the outer loop restarts.