This article
lists out extensive list of example scripts for looping through table records
one row at a time. This article covers the examples for the following
scenario’s for looping through table rows
- Looping column having no
gaps/duplicate values
- Looping column having gaps
- Looping column having duplicates
[ALSO
READ] WHILE loop in Sql Server
To understand the looping of the
table records in the above listed scenarios, let us first create a temporary
table #Student as shown in the below image with sample data using the following
script.
USE TEMPDB
GO
CREATE TABLE
#Student
(Id INT,
Name NVARCHAR(100), Status TINYINT)
GO
INSERT INTO #Student ( Id, Name, Status)
Values (1,
'Munesh sharma',
0),
(2, 'Rahul
sharma', 0),
(3, 'Roy
len', 0)
GO
|
The below examples illustrates how we can loop through table
records in various ways. And also highlights the problem if any. Please
go through all the examples before deciding on using one particular approach.
Example
1: Looping column having no gaps/duplicate values
Approach 1: Looping
through table records with static loop counter initialization
DECLARE @LoopNo INT = 1, @MaxStudentId INT = 3 ,
@StudentName NVARCHAR(100)
WHILE(@LoopNo <= @MaxStudentId)
BEGIN
SELECT
@StudentName = Name
FROM
#Student WHERE Id =
@LoopNo
PRINT
@StudentName
SET
@LoopNo =
@LoopNo +
1
END
|
In this
example the loop running variable @LoopNo and the maximum loop counter variable
@MaxStudentId values are initialized with a static value.
Note: This approach of looping through table rows doesn’t
work if the looping column (i.e. in this case Id column of the #Student table) values have gaps or if
it has duplicate values
Approach 2: Looping through table records with dynamic loop counter initialization
DECLARE @LoopNo INT , @MaxStudentId INT,
@StudentName NVARCHAR(100)
SELECT @LoopNo = min(id) , @MaxStudentId = max(Id)
FROM #Student
WHILE(@LoopNo IS NOT NULL
AND
@LoopNo <= @MaxStudentId)
BEGIN
SELECT
@StudentName = Name
FROM
#Student WHERE Id =
@LoopNo
PRINT
@StudentName
SET
@LoopNo =
@LoopNo +
1
END
|
In this
example the loop running variable @LoopNo and the maximum loop No variable @MaxStudentId
values are initialized dynamically.
Note: This approach of looping through table rows doesn’t
work if the looping column (i.e. in this case Id column of the #Student table) values have gaps or if
it has duplicate values
Example 2: Looping through table records
where looping column has gaps
Issue
with example 1’s approach 1 and 2: These example approaches are assuming that looping
column values doesn’t have any gap in it. Let us see what is the output of the
example 1’s approach 1 and 2 if we have gaps in the looping column value.
To create a gap, delete Student record from the #Student
table with id = 2 by the following script:
DELETE FROM #Student
WHERE Id = 2
GO
select * from #Student
|
Now let us run the example 1’s approach 1 and 2 script on #Student table
which is having gap in the Id column value (i.e. record with id column value 2
is missing).
From the
above result it is clear that the example 1’s approach 1 and 2 script will not
work in the scenarios where we have gap in the looping tables column values.
This problem can solved in multiple ways, below are
two such example approaches. I would prefer the first approach.
Example-2
Approach 1: Looping through table records where looping column has gaps in the
value
DECLARE @LoopNo INT , @MaxStudentId INT,
@StudentName NVARCHAR(100)
SELECT @LoopNo = min(id) , @MaxStudentId = max(Id)
FROM #Student
WHILE ( @LoopNo IS NOT NULL
AND @LoopNo <=
@MaxStudentId)
BEGIN
SELECT
@StudentName = Name FROM
#Student
WHERE Id = @LoopNo
PRINT
@StudentName
SELECT
@LoopNo =
min(id) FROM #Student
WHERE Id > @LoopNo
END
|
From the
above result it is clear that this script works even when we have gaps in the
looping column values.
Note: This approach of looping through table rows doesn’t
work if the looping column (i.e. in this case Id column of the #Student table) has duplicate values
Approach 2:
Looping through table records where looping column has gaps in the value
DECLARE @LoopNo INT , @MaxStudentId INT,
@StudentName NVARCHAR(100)
SELECT @LoopNo = min(id) , @MaxStudentId = max(Id)
FROM #Student
WHILE ( @LoopNo IS NOT NULL
AND @LoopNo <=
@MaxStudentId)
BEGIN
SELECT
@StudentName = Name
FROM
#Student WHERE Id =
@LoopNo
--To handle gaps
in the looping column value
IF(@@ROWCOUNT = 0 )
BEGIN
SET
@LoopNo =
@LoopNo +
1
CONTINUE
END
PRINT
@StudentName
SET
@LoopNo =
@LoopNo +
1
END
|
From the
above result it is clear that this script works even when we have gaps in the
looping column values.
Note: This approach of looping through table rows doesn’t
work if the looping column (i.e. in this case Id column of the #Student table) has duplicate values
Example 3: Looping through table records where looping
column having duplicates
To create a duplicate record, insert one more Student record
to the #Student table with id = 1 by the following script:
INSERT INTO #Student ( Id, Name, Status)
Values (1,
'Anshuman Sharma',
0)
Go
select * from #Student
|
Now let us run the example 2’s
approach 1 and 2 script on #Student table which is having duplicate Id column
values (i.e. there are two records with with Id column value as 1)
Print ' example 2 -
approach 1'
DECLARE @LoopNo INT , @MaxStudentId INT,
@StudentName NVARCHAR(100)
SELECT @LoopNo = min(id) , @MaxStudentId = max(Id)
FROM #Student
WHILE ( @LoopNo IS NOT NULL
AND @LoopNo <=
@MaxStudentId)
BEGIN
SELECT
@StudentName = Name FROM
#Student
WHERE Id = @LoopNo
PRINT
@StudentName
SELECT
@LoopNo =
min(id) FROM #Student
WHERE Id > @LoopNo
END
Go
Print ' example 2 -
approach 2'
DECLARE @LoopNo INT , @MaxStudentId INT,
@StudentName NVARCHAR(100)
SELECT @LoopNo = min(id) , @MaxStudentId = max(Id)
FROM #Student
WHILE ( @LoopNo IS NOT NULL
AND @LoopNo <=
@MaxStudentId)
BEGIN
SELECT
@StudentName = Name
FROM
#Student WHERE Id =
@LoopNo
--To handle gaps
in the looping column value
IF(@@ROWCOUNT = 0 )
BEGIN
SET
@LoopNo =
@LoopNo +
1
CONTINUE
END
PRINT
@StudentName
SET
@LoopNo =
@LoopNo +
1
END
|
From the
above result it is clear that the example 2’s approach 1 and 2 script will not
work in the scenarios where we have duplicates in the looping column. Here only
one record of the Student with id =1 is displayed and other record is skipped.
This problem can solved in multiple ways, below are two such example
approaches.
Approach 1: Looping through table records where looping column has duplicate values
SET NOCOUNT ON
DECLARE @LoopNo INT , @MaxStudentId INT,
@StudentName NVARCHAR(100)
SELECT @LoopNo = min(id) , @MaxStudentId = max(Id)
FROM #Student
WHILE ( @LoopNo IS NOT NULL
AND @LoopNo <=
@MaxStudentId)
BEGIN
UPDATE TOP(1) #Student
SET Status = 1, @StudentName = Name
WHERE Id = @LoopNo AND Status = 0
PRINT
@StudentName
SELECT
@LoopNo =
min(id) FROM
#Student
WHERE Id >= @LoopNo AND Status = 0
END
|
In this
approach using the Status column to mark the records which are already
processed. And also the update statement is used to update the status and also
get the row values and one more thing is in Update using the TOP statement to
update only one record at a time.
Approach 2: Looping through table records where looping column has duplicate values
by inserting records into another temp table with identity column
--Create another temp table with identity column
CREATE TABLE
#StudentCopy (LoopId
INT IDENTITY(1,1),
Id INT, Name NVARCHAR(100), Status TINYINT)
--Copy data to the table with identity column
INSERT INTO
#StudentCopy(Id,
Name, Status)
SELECT Id, Name, Status FROM #Student
DECLARE @LoopNo INT , @MaxStudentId INT,
@StudentName NVARCHAR(100)
SELECT @LoopNo = min(LoopId),@MaxStudentId = max(LoopId)
FROM #StudentCopy
WHILE ( @LoopNo IS NOT NULL
AND @LoopNo <=
@MaxStudentId)
BEGIN
SELECT
@StudentName = Name
FROM
#StudentCopy WHERE
LoopId = @LoopNo
PRINT
@StudentName
SELECT
@LoopNo =
min(LoopId)
FROM
#StudentCopy WHERE
LoopId > @LoopNo
END
|
EmoticonEmoticon