Understand SQL WHILE loop with examples

This tutorial will explain what a while loop is in SQL. It will also explain how to use the SQL WHILE loop. We'll look at the underlying logic and the syntax. We'll also look at different examples of how to use a WHILE loop.

The SQL WHILE loop is helpful to execute the SQL Statement(s) repeatedly until the condition in the while loop is false.

The While loop starts with a loop condition. SQL Server evaluates the loop condition and executes the specified SQL statements if it is true. If the while condition is false, SQL Server exists the loop. Therefore, the SQL statement can run zero or multiple times.

The syntax of the SQL WHILE loop is as below.

WHILE (condition)
BEGIN
   {
                    sql_statement | statement_block | BREAK | CONTINUE 
    }
END

The following query declares a variable @i and sets its value to 1. Later, the while loop checks for a condition (@i<=5).

The current value of @i is 1. Therefore, it satisfies the while loop condition and starts executing the SQL statement in the BEGIN END block.

For this demo, it prints the loop execution number and increments the variable @i with 1. Now, the while block checks the new value of @i (2<=5) and starts executing code. The process follows until the value of variable @i is less than or equal to 5.

DECLARE @i INT 
SET @i=1
WHILE ( @i <= 5)
BEGIN
    PRINT 'Loop Execution Number ' + CONVERT(VARCHAR,@i)
    SET @i  = @i  + 1
END

Break clause in SQL WHILE LOOP

We can use the BREAK Clause to stop the loop execution immediately.

For example, let’s say, in the previous example, we do not want to print the value if the variable @i value is greater than 8. Here, we can use the IF ELSE statement to check the current iteration value for @i and use the break clause to exit the while loop if its value exceeds 8.

DECLARE @i INT 
SET @i=1
PRINT 'Use of BREAK in WHILE LOOP'
WHILE ( @i <= 10)
BEGIN
Print 'Current value:  '  + CONVERT(VARCHAR,@i)
SET @i  = @i  + 1  
    If (@i >8)
                BEGIN  
                                PRINT 'Loop Execution Number ' + CONVERT(VARCHAR,@i) + ' therefore, exit the loop'
                                BREAK  
                END  
END

Use of CONTINUE in SQL WHILE LOOP

The CONTINUE clause restarts the while loop and ignores the statements after the CONTINUE keyword. In the below example, we used CONTINUE block to restart while loop if the parameter value @i is even number and check for the next @i value.

DECLARE @i INT 
SET @i=1
PRINT 'Use of Contiinue in WHILE LOOP'
WHILE ( @i <= 10)
                BEGIN
                                IF (@i%2=0)
                                BEGIN
                                                SET @i  = @i + 1  
                                                CONTINUE
                                END
                PRINT 'Odd value is ' + CONVERT(VARCHAR,@i) 
                SET @i  = @i + 1  
                END   

The following T-SQL script does the following tasks.

Firstly, evaluates the avg list price from Production.Product table using AVG() function. If the avg price is less than $300, it enters into BEGIN loop and updates the list price.

If the new list price is greater than $500, it breaks the while loop. Else it continues the loop execution.

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  

This article taught us how to implement iterative operations in SQL Server by using the WHILE loop. The BREAK and CONTINUE clauses can control the iteration of a SQL WHILE loop. We also virtualized flowcharts of the WHILE loop.