Simple Example Of While Loop 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. 
  1. USE AdventureWorks;  
  2. GO  
  3. DECLARE @Flag INT  
  4. SET @Flag = 1  
  5. WHILE (@Flag < 10)  
  6. BEGIN  
  7. BEGIN  
  8. PRINT @Flag  
  9. SET @Flag = @Flag + 1  
  10. END  
  11. IF(@Flag > 5)  
  12. BREAK  
  13. ELSE  
  14. CONTINUE  
  15. END  
While loop can use Select queries as well. You can find the example given below of BOL, which is very useful. 
  1. USE AdventureWorks;  
  2. GO  
  3. WHILE (  
  4. SELECT AVG(ListPrice)  
  5. FROM Production.Product) < $300  
  6. BEGIN  
  7. UPDATE Production.Product  
  8. SET ListPrice = ListPrice * 2  
  9. SELECT MAX(ListPrice)  
  10. FROM Production.Product  
  11. IF (  
  12. SELECT MAX(ListPrice)  
  13. FROM Production.Product) > $500  
  14. BREAK  
  15. ELSE  
  16. CONTINUE  
  17. END  
  18. PRINT 'Too much for the market to bear';  
Ebook Download
View all
Learn
View all