Chapter 1: Commands

break

Description

Causes an exit from a while loop. break is often activated by an if test.

Syntax

while logical_expression 
	statement 
break 
	statement 
continue

Parameters

logical_expression: is an expression (a column name, constant, any combination of column names and constants connected by arithmetic or bitwise operators, or a subquery) that returns TRUE, FALSE, or NULL. If the logical expression contains a select statement, enclose the select statement in parentheses.

Examples

Example 1

If the average price is less than $30, double the prices. Then, select the maximum price. If it is less than or equal to $50, restart the while loop and double the prices again. If the maximum price is more than $50, exit the while loop and print a message:

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 
 begin 
  print "Too much for the market to bear"
 end

Usage

break causes an exit from a while loop. Statements that appear after the keyword end, which marks the end of the loop, are then executed.
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; then, the next outermost loop restarts.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

break permission defaults to all users. No permission is required to use it.