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';