Nested SQL While Loop

The Nested SQL While Loop is nothing but placing While Loop inside another While Loop. Nested loops are very useful, When you are working with multi-layered data because when we want to select the layered data, we have to use this Nested While loops to extract the layered data, but please be careful while using it. Before we get into the example, let us see the syntax of Nested SQL While loop.

Nested SQL While Loop Syntax

The basic syntax of the Nested While Loop in SQL Server 2014 is as follows:

If you observe the above syntax, We placed the While loop inside another While loop. We already explained the While loop syntax in our previous article so, please refer SQL While Loop article to understand the loop functionality.

Step 1: First, Compiler will check for the condition inside the first While loop.

• If the expression is True then the group of statements inside the While loop will be executed. It means, compiler will enter into second While loop. Go to Step 2
• If the condition is False then, it will exit from While Loop

Step 2: Compiler will check for the condition inside the second While loop.

• If the condition is True then the group of statements inside the second While loop will be executed. It means, SQL Server will execute statements from Statement 1 to N.
• If the condition is False then, it will exit from second While Loop

Step 3: Once it exit from second While loop, compiler will check for the condition inside the first While loop (repeating Step 1 )

Nested SQL While Loop Example

This program will print the Multiplication table of 1 and 2 up to 10. To do this, we are going to nest one While loop inside another While loop, this is also called as nested SQL While Loop.

SQL CODE

OUTPUT

ANALYSIS

First, we created two variables called Val1, Val2 and then initialized the @Val1 to 1 using the following statement

In the next line we used while loop with expression. If the expression result is true then, it will enter to Nested While loop.

From the above screenshot you can observe that, this program prints multiplication table for 1 and 2.

First While Loop First Iteration

In the first While loop @Val1 is 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 second While loop

Second While Loop First Iteration

In the second While loop @Val2 is initialized to 1, and then it will check whether @Val2 is less than or equal to 10. This condition is True so, compiler will execute the statements inside the second While loop 

@Val1 * @Val2 ==> 1 * 1 = 1

Next, the @Val2 value will be incremented by 1 (SET @Val2 = @Val2 + 1). Please refer SQL Arithmetic Operators article to understand the + notation.

Second While Loop Second Iteration

Here, @Val2 is incremented by 1 so, @Val2 =2. Compiler 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 second 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

Here, @Val1 is incremented by 1 so, @Val1 = 2. Compiler will check whether @Val1 is less than or equal to 2. This condition ( 2 <= 2) is True so, compiler will enter into second While loop

Second While Loop First Iteration

Within the second While loop @Val2 is initialized to value 1, and then it will check whether @Val2 is less than or equal to 10. This condition is True so, compiler will execute 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 be repeat until @Val2 reaches to 11. Once it reaches to 11, the condition (11 <= 10) inside the second While loop fails, so compiler will exit from second While loop and @Val1 value will be incremented by 1 (SET @Val1 = @Val1 + 1).

First While Loop Third Iteration:

Here, i = 3 it means the expression (@Val1 <= 2) will be False so, While loop is terminated. Remember, No need to check the second loop

Thank you for visiting our Blog