The Nested SQL While Loop is nothing but placing While Loop inside another While Loop. SQL Server Nested While loops are instrumental when you are working with multi-layered data. Because when we want to select the layered data, we have to use this SQL Nested While loops to extract the layered data. But please be careful while using it.
Nested SQL While Loop Syntax
The syntax of the Nested While Loop in SQL Server is
WHILE Expression BEGIN WHILE @Val2 <= 10 BEGIN -- Second While Loop Statements Statement 1 Statement 2 ........... Statement N END -- Below Statements are Outside the Second While Loop -- First While Loop Statements Statement 1 Statement 2 ........... Statement N END -- This Statement is Outside the First While Loop
If you observe the above Nested SQL While Loop syntax, We placed the While loop inside another While loop. We already explained the While loop syntax in our previous article. So, please refer to While Loop article.
Step 1: First, it checks for the condition inside the first While loop.
- If the expression result is True, the begin and end code block inside the While loop will execute. Next, it will start the second While loop. Go to Step 2
- If the result is False, it will exit from While Loop
Step 2: It will verify the condition in the Nested SQL While Loop (second While loop).
- If the result is True, the code inside the second While loop begin…end will execute. It means SQL Server run statements from Statement 1 to N.
- If it is False, it exits from second While Loop
Step 3: Once exit from second While loop, it will check for the condition inside the first While loop (repeating Step 1 )
Nested SQL While Loop Example
This SQL Nested While loop program will print the Multiplication table of 1 and 2 up to 10.
For this, we will nest one While loop inside another While loop, also called nested SQL While Loop.
--Nested SQL While Loop Example DECLARE @Val1 INT, @Val2 INT SET @Val1 = 1 WHILE @Val1 <= 2 BEGIN SET @Val2 = 1 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

Within this SQL Nested While Loop example, First, we created two variables called Val1, Val2 and then initialized the @Val1 to 1 using the following statement
DECLARE @Val1 INT, @Val2 INT SET @Val1 = 1
In the next line, we used the SQL Server while loop with expression. If the expression result is true then, it will enter to Nested While loop.
WHILE @Val1 <= 2 BEGIN SET @Val2 = 1 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
From the above screenshot you can observe that, this Nested SQL While Loop query prints multiplication table for 1 and 2.
First While Loop First Iteration
In the first While loop, @Val1 initialized to 1, and then it will check whether @Val1 is less than or equal to 2. This condition (1 <= 2) is True so, it will enter into a second While loop
Nested SQL While Loop or Second While Loop First Iteration
In the second While loop, @Val2 initialized to 1, and check whether @Val2 is less than or equal to 10. This condition is True. So, the statements inside the second While loop will execute
PRINT CONVERT(VARCHAR, @Val1) + ' * ' + CONVERT(VARCHAR, @Val2) + ' = ' + CONVERT(VARCHAR, @Val1 * @Val2)
@Val1 * @Val2 ==> 1 * 1 = 1
Next, the @Val2 value will be incremented by 1 (SET @Val2 = @Val2 + 1). Please refer Arithmetic Operators article to understand the + notation.
Second While Loop Second Iteration
Here, @Val2 incremented by one so, @Val2 =2. It will check whether @Val2 is less than or equal to 10. This condition (2 <= 10) is True so, it will execute the statements inside the second While loop
@Val1 * @Val2 ==> 1 * 2 = 2
Next, the @Val2 value will be incremented by 1
This process will repeat until @Val2 reaches to 11. Once the condition inside the SQL nested While loop fails, compiler will exit from second While loop and @Val1 value will be incremented by 1 (SET @Val1 = @Val1 + 1).
First While Loop Second Iteration
@Val1 = 2. Check whether @Val1 is less than or equal to 2. This condition ( 2 <= 2) is True so, it enters into a second While loop
Second While Loop First Iteration
Within the second While loop, @Val2 initialized to value 1 and check whether @Val2 is less than or equal to 10. This condition is True, so it executes the statements inside the second While loop
@Val1 * @Val2 ==> 2 * 1 = 2
Next, the @Val2 value will be incremented by 1 (SET @Val2 = @Val2 + 1).
Second While Loop Second Iteration
Here, @Val2 is incremented by 1 so, @Val2 = 2. Next, it will check whether @Val2 is less than or equal to 10. The condition (2 <= 10) is True so, it will start executing the statements inside the second While loop
@Val1 * @Val2 ==> 2 * 2 = 4
Next, the @Val2 value will be incremented by 1 (SET @Val2 = @Val2 + 1)
This process will repeat until @Val2 reaches to 11.Once it reaches to 11, the condition (11 <= 10) inside the second While loop fails. So, it will exit from the nested While loop and @Val1 value will be incremented by 1 (SET @Val1 = @Val1 + 1).
First While Loop Third Iteration:
Here, i = 3 means the expression (@Val1 <= 2) will be False. So, While loop terminated. Remember, No need to check the second loop.
Comments are closed.