Simple Example Of While Loop With Break And Continue In SQL Query

In this blog, you will learn a simple example of While lop with Break and Continue in SQL query.

While statement sets a condition for the repeated execution of SQL statement or statement block. A very simple example is given below of While loop with Break and Continue. 

USE AdventureWorks;
GO
DECLARE @Flag INT
SET @Flag = 1
WHILE (@Flag < 10)
BEGIN
BEGIN
PRINT @Flag
SET @Flag = @Flag + 1
END
IF(@Flag > 5)
BREAK
ELSE
CONTINUE
END

While loop can use Select queries as well. You can find the example given below of BOL, which is very useful. 

USE AdventureWorks;
GO
WHILE (
SELECT AVG(ListPrice)
FROM Production.Product) < $300
BEGIN
UPDATE Production.Product
SET ListPrice = ListPrice * 2
SELECT MAX(ListPrice)
FROM Production.Product
IF (
SELECT MAX(ListPrice)
FROM Production.Product) > $500
BREAK
ELSE
CONTINUE
END
PRINT 'Too much for the market to bear';