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.