SQL Server WHILE LOOP

This article will teach you about a complete overview of using the WHILE loop in SQL Server. A WHILE loop is a control flow statement used to repeatedly execute the set of statements until the specified condition is satisfied. This loop begins with a given condition, evaluate it, and if it is TRUE, the statements will go inside the loop for further execution. If the condition becomes FALSE, it will not run. It implies that the while loop in SQL Server could run for zero or more times.

Flowchart of WHILE Loop

The following flowchart will explain the complete workflow of the WHILE loop within SQL Server:

We can see in this chart that the specified condition is checked for each iteration, and based on the evaluation result, the code flow is determined. If the result is evaluated TRUE, the control flow goes inside the loop for further execution. If the evaluated result is FALSE, the control flow will exit the loop, and any statement or query outside of the loop will be executed.

Syntax

The following syntax illustrates the WHILE loop in SQL Server:

WHILE boolean_condition
BEGIN
   {SQL_statement | statement_block | BREAK | CONTINUE}
END;

In this syntax, we have the following parameters or arguments:

boolean_condition: It's a required condition that will be tested in each iteration to return the TRUE or FALSE result. If it is the SELECT statement, it must be surrounded in parenthesis.
sql_statement or statement_block: The SQL statement or grouping is defined inside the BEGIN and END keywords. It will be executed in each iteration until the loop becomes FALSE.
Break: It ends the innermost loop instantly, and control flow resumes at the next statement after the loop.
Continue: It jumps to the next iteration without skipping the remaining statements inside the loop. Usually, it causes the loop to restart from the beginning.

WHILE Loop Example

Let us understand how the WHILE loop works in SQL Server through an example. In the given example, we have first declared a value of integer type and set its value to 1. Next, the WHILE loop checks the condition, and if it is TRUE, the print statement will be printed. When the loop becomes FALSE, the next statement after the WHILE loop will be printed.

DECLARE @stud_value INT;
SET @stud_value = 1;
WHILE @stud_value <= 5
 BEGIN
  PRINT 'Mark Henry';
  SET @stud_value = @stud_value + 1;
 END;
PRINT 'Rose Bennet';

Executing this statement will return the following output:

In the above WHILE loop code snippet, we must increment the variable's value after each iteration. See the below part of the above code line as SET @stud_value = @stud_value + 1. If we do not write this statement, the loop will execute infinitely because it cannot becomes FALSE.

BEGIN
  PRINT 'Mark Henry';
  SET @stud_value = @stud_value + 1;
 END;

Infinite WHILE Loop

An infinite loop occurs when the evaluation of a condition will never be false. Therefore, the loop will never end and be executed forever. The loop in the following code snippet is infinite because the variable's value is not incremented.

DECLARE @stud_value INT;
SET @stud_value = 1;
WHILE @stud_value <= 5
 BEGIN
  PRINT 'Please stop execution!'
 END;

Executing the loop will display the below output. This loop will never end its execution until we do not cancel their execution of the query manually.

Inserting Records with WHILE Loop

We can also use the WHILE loop to insert records into the defined table. Let us see how to inserts dummy records into the database. First, we will create a table named 'bikeshop' containing three columns: Id, bike_name, and price. Execute the following statement to create this table:

CREATE TABLE bikeshop
(
 Id INT PRIMARY KEY IDENTITY,
 bike_name VARCHAR (50) NOT NULL,
 price FLOAT
)

Next, we will use the WHILE loop to insert ten records into this table by executing the following script:

DECLARE @count INT;
SET @count = 1; 
WHILE @count <= 10
BEGIN
   INSERT INTO bikeshop VALUES('Bike-' + CAST(@count as varchar), @count*5000)
   SET @count = @count + 1;
END;

In this code, we have declared a variable @count and then initialize its value with 1 using a SET clause. Next, we have to define the loop body that executes the INSERT statement to add one record in each execution. The bike_name column will append the value of a @count variable with the string Bike, and the price column determines by the value of a @count variable multiplied by 5000. The loop will execute until the value of the @count variable becomes FALSE. It means the WHILE loop will execute ten times and inserts ten records into the table bikeshop.

Now, we can verify all the records of the bikeshop table with the SELECT statement. It will display the following output:

BREAK Statement

SQL Server also allows us to use the BREAK statement in the WHILE loop like programming languages. This statement is used to immediately stop the current iteration of the loop, and control flow resumes with the next statement after the loop. In general, we will use the IF...ELSE statement to check whether or not a condition has occurred.

The following example will explain how to use the BREAK statement in the WHILE loop:

DECLARE @count INT;
SET @count = 1; 
WHILE @count <= 10
BEGIN
  PRINT 'BREAK Statement Example'
  IF @Count >= 6
  BEGIN
   BREAK
  END
  SET @Count  = @Count + 1
END;

Executing the code will display the below output:

The value of the variable is first evaluated in this code. If it is TRUE, the control enters into the loop and prints the statement. When the variable value is greater than or equal to 6, control enters the IF...ELSE block and executes the BREAK statement to terminate the loop. If an IF...ELSE block fails to meet the condition; then, the loop will keep running until the condition is changed to FALSE.

CONTINUE Statement

SQL Server also allows us to use the CONTINUE statement in the WHILE loop like programming languages. This statement immediately terminates the current execution of the loop when the specified condition is met, and control flow returns to the beginning of the loop. In general, the IF...ELSE statement will be used to test whether or not a condition has been met.

The CONTINUE statement in the WHILE loop is demonstrated in the following example. In this example, we'll assume that we wish to use a WHILE loop to print only odd values. The CONTINUE statement can be used to do this. This example will first test whether the variable value is odd or even. If it is even, the execution goes inside the IF…ELSE statement blocks and decrement the variable value by one. Then, it will execute the CONTINUE statement and starts a new iteration from the beginning.

DECLARE @Count INT 
SET @Count = 1
WHILE (@Count <= 20)
BEGIN
  IF @Count % 2 = 0
  BEGIN
  SET @Count = @Count + 1
  CONTINUE
  END
    PRINT 'The odd value is = ' + CONVERT(VARCHAR, @Count)
    SET @Count = @Count + 1
END 

Executing the code snippet will display the below output:

How to implementing paging with WHILE loop in SQL Server?

We can also use the WHILE loop for implementing the paging. Paging allows displaying the subset of records from a table at any particular time. The following example will explain this concept. The WHILE loop in the code will select two records from the bikeshop table at a time. The records that have been chosen are then displayed in the output.

DECLARE @count INT
DECLARE @limit INT;
SET @count = 0
SET @limit = 2;
WHILE @count < 10
BEGIN
   SELECT * FROM bikeshop ORDER BY Id
   OFFSET @count ROWS
   FETCH NEXT @limit ROWS ONLY
   SET @count = @count + 2;
END;

Executing the code snippet will return the below output:

Nested WHILE Loop

The Nested WHILE Loop in SQL Server is simply a WHILE Loop written inside another WHILE Loop. When we work on multi-layered data, the Nested WHILE loops are essential. Because this concept is useful in extracting the layered data when we want to select them, it is recommended to be careful while using the nested loop.

Syntax

The following syntax illustrate the working of the nested WHILE Loop in SQL Server:

WHILE Expression
 BEGIN
	WHILE @Val2 <= 10
		BEGIN
			--Second WHILE Loop Statements
			SQL Statements
		END
	--This statement is outside the second WHILE loop
	--Which is the first WHILE loop statement
	SQL Statements
 END
-- This statement is outside the first WHILE loop

Let us explain this syntax step by step:

Step 1: The loop starts by checking the first WHILE loop condition, and if it finds a false result, it will exit from While Loop. Otherwise, if the result is true, the control goes inside the BEGIN and END block for further execution. This block will start the execution of the second WHILE loop. See step 2.

Step 2: This step will check the condition in the Nested WHILE Loop, and if it is false, the second loop will be exit and execute the statement outside this. Otherwise, if the result is true, the control goes inside the BEGIN and END block for further execution.

Step 3: Once all the statements execute from the second WHILE loop, the control goes to the first WHILE and repeats the first step.

Example

The following example will print the multiplication table of 5 up to 10 using the nested WHILE loop.

DECLARE @val1 INT
DECLARE @val2 INT
SET @val1 = 5
SET @val2 = 1 
WHILE @val1 <= 5
BEGIN     
 WHILE @val2 <= 10   
  BEGIN       
   PRINT CONVERT(VARCHAR, @val1) + ' * ' + CONVERT(VARCHAR, @val2)
      + ' = ' + CONVERT(VARCHAR, @val1 * @val2)       
   SET @val2 = @val2 + 1   
  END   
 SET @val1 = @val1 + 1
END 

Executing the statement will display the multiplication table of 5. Here is the output:

Conclusion

The WHILE loop is a useful method when there is a need to execute a SQL script repeatedly. The article explained how to work with the WHILE loop in MS SQL Server to execute operations such as record insertion and pagination with a simple example. Here we have also learned the BREAK and CONTINUE statements to control the WHILE loop iteration.

Next TopicFind and Delete Duplicates From a Table in SQL Server

← prev next →