Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

Nested SQL While Loop

by suresh

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 SQL 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

OUTPUT

Nested SQL While Loop 1

ANALYSIS

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.

Placed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy