Nested Looping in SQL Server
This article summaries the use of While Loop in SQL Server. I have explained the use of Single While Loop and Nested While Loop (Nested Level is 2). The cursor also would solve this purpose but it will result in performance issue. when compared to Cursor the While Loop usage is simple and easiest one.
While Loop Definition :
To do repeated execution of set of SQL Statement(s).
Note: Cursor can also be used for this purpose
Syntax
WHILE Boolean_expression
{ sql_statement | statement_block | BREAK | CONTINUE }
Example
WHILE (@iLoopCounter <= @MaxCount)
BEGIN
--Execute SQL Statement
Set @iLoopCounter = @iLoopCounter +1
End
Consider the Master Data set up for the usage of the While Loop
1. First Level Data Setup
Create table Student (Studentid int, StudentName varchar(50), StudentStatus varchar(50))
Insert Student values(1,'Vijay','ACTIVE')
Insert Student values(2,'Mahe','INACTIVE')
Insert Student values(3,'Prasanna','ACTIVE')
Select * from Student
Create table Assignment (Assignmentid int, AssignmentName varchar(50))
Insert Assignment values (101, 'English')
Insert Assignment values (102, 'Tamil')
Insert Assignment values (103, 'Maths')
Insert Assignment values (104, 'Science')
Insert Assignment values (105, 'Social')
Create table StudentAssignment (StudentAssignmentid int identity(1,1), Studentid int, Assignmentid varchar(50), AssignmentStatus varchar(50))
/*
2. Modification/Addition in the Master Table
Insert Student values(4,'Abdullah','Active')
Insert Assignment values (106, 'GK')
*/ Usage of Single While Loop
Req: Need to update the Inactive user to Active user
declare @LoopCounter int
declare @MaxCount int
Create table #Student (id int, Studentid int, StudentName varchar(50), StudentStatus varchar(50))
-- Resultset which has to be modified
Insert #Student
Select ROW_NUMBER() OVER (ORDER BY Studentid ASC) as rownum, Studentid, StudentName, StudentStatus
from Student where StudentStatus = 'INACTIVE'
set @LoopCounter =1
Select @MaxCount = COUNT(1) from #Student
WHILE (@LoopCounter <= @MaxCount)
BEGIN
-- Action to be taken on each record of the Result
if exists ( Select Student.Studentid from Student Student, #Student TempStud where Student.Studentid = TempStud.Studentid
and id =@LoopCounter )
Begin
Update Student set StudentStatus ='ACTIVE'
from #Student TempStud where
Student.Studentid = TempStud.Studentid
and id =@LoopCounter
End
-- Loop incrementor
Set @LoopCounter = @LoopCounter +1
End
drop table #StudentUsage of Nested While Loop
Req: Need to create a record for each student for each Assignment in StudentAssignment Table
declare @iLoopCounter int
declare @jLoopCounter int
declare @Table1MaxCount int
declare @Table2MaxCount int
Create table #Student (id int, Studentid int, StudentName varchar(50), StudentStatus varchar(50))
Create table #Assignment (id int, Assignmentid int, AssignmentName varchar(50))
Insert #Student
Select ROW_NUMBER() OVER (ORDER BY Studentid ASC) as rownum, Studentid, StudentName, StudentStatus from Student
Insert #Assignment
Select ROW_NUMBER() OVER (ORDER BY Assignmentid ASC) as rownum, Assignmentid, AssignmentName from Assignment
set @iLoopCounter =1
set @jLoopCounter =1
Select @Table1MaxCount = COUNT(1) from #Student
Select @Table2MaxCount = COUNT(1) from #Assignment
WHILE (@iLoopCounter <= @Table1MaxCount)
BEGIN
set @jLoopCounter =1
WHILE (@jLoopCounter <= @Table2MaxCount)
BEGIN
if not exists (select StudentAssignmentid from StudentAssignment StudAssign, #Student Stud, #Assignment Assign where StudAssign.Studentid = Stud.Studentid and StudAssign.Assignmentid = Assign.Assignmentid and Stud.id= @iLoopCounter and Assign.id= @jLoopCounter)
Begin
Insert StudentAssignment ( Studentid, Assignmentid, AssignmentStatus)
Select (Select Studentid from #Student where #Student.id=@iLoopCounter), (Select Assignmentid from #Assignment where #Assignment.id=@jLoopCounter), 'SUBMITTED'
End
Set @jLoopCounter = @jLoopCounter +1
End
Set @iLoopCounter = @iLoopCounter +1
End
drop table #Student
drop table #Assignment
Advantages of While Loop over Cursor
1. WHILE loops are as easy to use as a cursor.
2. Cursor is an actual object inside the database engine, there is a little overhead involved in creating the cursor and destroying it.
3. Usage of Cursor can lead to performance issue. We need to deallocate the cursor after its usage.