SQL Server/T-SQL Tutorial/Transact SQL/Break
Break out of a while loop
<source lang="sql">
3> CREATE TABLE Product( 4> ProductID int NOT NULL, 5> Name nvarchar(25) NOT NULL, 6> ProductNumber nvarchar(25) , 7> Color nvarchar(15) NULL, 8> StandardCost money NOT NULL, 9> Size nvarchar(5) NULL, 10> Weight decimal(8, 2) NULL, 11> ProductLine nchar(20) NULL, 12> SellStartDate datetime NOT NULL, 13> SellEndDate datetime NULL 14> ) 15> GO 1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22"); 2> GO (1 rows affected) 1> 2> 3> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22"); 4> GO (1 rows affected) 1> 2> 3> 4> 5> WHILE (SELECT AVG(StandardCost) FROM Product) < $1200 6> BEGIN 7> UPDATE Product SET StandardCost = StandardCost * 1.25 8> SELECT MAX(StandardCost) FROM Product 9> IF (SELECT MAX(StandardCost) FROM Product) > $4000 10> 11> BREAK 12> ELSE 13> 14> CONTINUE 15> END 16> PRINT "Done." 17> GO (10 rows affected)
987.2363
(10 rows affected)
1234.0454
(10 rows affected)
1542.5568
(10 rows affected)
1928.1960
(10 rows affected)
2410.2450
(1 rows affected) Done. 1> 2> 3> drop table Product; 4> GO 1> 2></source>
