Tag Archives: WHILE

WHILE loop in Sql Server

WHILE loop is the looping construct supported by Sql Server. Sql server doesn’t have for…loop, do…while loop etc, but with WHILE loop we can simulate these missing looping constructs behaviour.

This article will cover the following:

WHILE LOOP

A while loop will check the condition first and then executes the block of Sql Statements within it as along as the condition evaluates to true.
Syntax:
WHILE Condition
BEGIN
Sql Statements
END

Example: Basic while loop example. The below while loop executes the statements within it 4 times.

DECLARE @LoopCounter INT = 1
WHILE ( @LoopCounter <= 4)
BEGIN
	PRINT @LoopCounter  
	SET @LoopCounter  = @LoopCounter  + 1
END	

RESULT:
1
2
3
4

BREAK Statement

If a BREAK statement is executed within a WHILE loop, then it causes the control to go out of the while loop and start executing the first statement immediately after the while loop.

Example: WHILE loop with BREAK statement

DECLARE @LoopCounter INT = 1
WHILE ( @LoopCounter <= 4)
BEGIN
	PRINT @LoopCounter  
	IF(@LoopCounter = 2)
		BREAK
 	SET @LoopCounter  = @LoopCounter  + 1
END
PRINT 'Statement after while loop'

RESULT:
WHILE Loop Break Statement Sql Server

CONTINUE Statement

If a CONTINUE statement is executed within a WHILE loop, then it skips executing the statements following it and transfers control to the beginning of while loop to start the execution of the next iteration.

Example: WHILE loop with CONTINUE statement

DECLARE @LoopCounter INT = 0
	WHILE ( @LoopCounter <= 3)
	BEGIN
		SET @LoopCounter  = @LoopCounter  + 1 

		IF(@LoopCounter = 2)
		  CONTINUE
 
		PRINT @LoopCounter 
	END
	PRINT 'Statement after while loop'

RESULT:
WHILE Loop Continue Statement Sql Server

DO…WHILE Loop in Sql Server

Sql Server doesn’t have a DO…WHILE loop construct, but same behaviour can be achieved using a WHILE loop as shown by the below example.

DECLARE @LoopCounter INT = 1
WHILE(1=1)
BEGIN
   PRINT @LoopCounter
   SET @LoopCounter  = @LoopCounter  + 1 	
   IF(@LoopCounter > 4)
	BREAK;		   
END

RESULT:
DO WHILE Loop in Sql Server

Looping through table records one row at a time

Article Looping through table records in Sql Server lists out the various approaches of looping through table records row by row.

[ALSO READ] PRINT/SELECT Statement messages within WHILE LOOP or BATCH of statement is not displayed immediately after it’s execution- Sql Server

Looping through table records in Sql Server

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

  1. Looping column having no gaps/duplicate values
  2. Looping column having gaps
  3. 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 #Employee as shown in the below image with sample data using the following script.

WHILE Loop Example Sql Server
Script:

USE TEMPDB
GO
CREATE TABLE #Employee
(Id INT, Name NVARCHAR(100), Status TINYINT)
GO
INSERT INTO #Employee ( Id, Name, Status)
Values (1, 'Basavaraj Biradar', 0),
		(2, 'Shree Biradar', 0),
		(3, 'Kalpana Biradar', 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 @LoopCounter INT = 1, @MaxEmployeeId INT = 3 , 
        @EmployeeName NVARCHAR(100)

WHILE(@LoopCounter <= @MaxEmployeeId)
BEGIN
   SELECT @EmployeeName = Name 
   FROM #Employee WHERE Id = @LoopCounter

   PRINT @EmployeeName	
   SET @LoopCounter  = @LoopCounter  + 1 	    
END

RESULT:
Looping through table records Sql Server 1

In this example the loop running variable @LoopCounter and the maximum loop counter variable @MaxEmployeeId 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 #Employee table) values have gaps or if it has duplicate values

Approach 2: Looping through table records with dynamic loop counter initialization

DECLARE @LoopCounter INT , @MaxEmployeeId INT, 
        @EmployeeName NVARCHAR(100)
SELECT @LoopCounter = min(id) , @MaxEmployeeId = max(Id) 
FROM #Employee

WHILE(@LoopCounter IS NOT NULL 
      AND @LoopCounter <= @MaxEmployeeId)
BEGIN
   SELECT @EmployeeName = Name 
   FROM #Employee WHERE Id = @LoopCounter
   
   PRINT @EmployeeName	
   SET @LoopCounter  = @LoopCounter  + 1 	    
END

RESULT:
Looping through table records Sql Server 2

In this example the loop running variable @LoopCounter and the maximum loop counter variable @MaxEmployeeId 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 #Employee 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 employee record from the #Employee table with id = 2 by the following script:

DELETE FROM #EMPLOYEE WHERE Id = 2

RESULT:
Looping through table records Sql Server 3

Now let us run the example 1’s approach 1 and 2 script on #Employee table which is having gap in the Id column value (i.e. record with id column value 2 is missing).

Looping through table records Sql Server 12

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.

Approach 1: Looping through table records where looping column has gaps in the value

DECLARE @LoopCounter INT , @MaxEmployeeId INT, 
        @EmployeeName NVARCHAR(100)
SELECT @LoopCounter = min(id) , @MaxEmployeeId = max(Id) 
FROM #Employee

WHILE ( @LoopCounter IS NOT NULL 
        AND  @LoopCounter <= @MaxEmployeeId)
BEGIN
   SELECT @EmployeeName = Name FROM #Employee 
   WHERE Id = @LoopCounter
   PRINT @EmployeeName	
   SELECT @LoopCounter  = min(id) FROM #Employee
   WHERE Id > @LoopCounter
END

RESULT:
Looping through table records Sql Server 6

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 #Employee table) has duplicate values

Approach 2: Looping through table records where looping column has gaps in the value

DECLARE @LoopCounter INT , @MaxEmployeeId INT, 
        @EmployeeName NVARCHAR(100)
SELECT @LoopCounter = min(id) , @MaxEmployeeId = max(Id) 
FROM #Employee
WHILE ( @LoopCounter IS NOT NULL 
        AND  @LoopCounter <= @MaxEmployeeId)
BEGIN
   SELECT @EmployeeName = Name 
   FROM #Employee WHERE Id = @LoopCounter
   --To handle gaps in the looping column value
   IF(@@ROWCOUNT = 0 )
   BEGIN
	 SET @LoopCounter  = @LoopCounter  + 1 
	 CONTINUE
   END

   PRINT @EmployeeName	
   SET @LoopCounter  = @LoopCounter  + 1 	    
END

Looping through table records Sql Server 7

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 #Employee table) has duplicate values

Example 3: Looping through table records where looping column having duplicates

To create a duplicate record, insert one more employee record to the #Employee table with id = 1 by the following script:

INSERT INTO #Employee ( Id, Name, Status)
Values (1, 'Sharan Biradar', 0)

RESULT:
Looping through table records Sql Server 8

Now let us run the example 2’s approach 1 and 2 script on #Employee table which is having duplicate Id column values (i.e. there are two records with with Id column value as 1)

Looping through table records Sql Server 13

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 employee 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 @LoopCounter INT , @MaxEmployeeId INT, 
        @EmployeeName NVARCHAR(100)
SELECT @LoopCounter = min(id) , @MaxEmployeeId = max(Id) 
FROM #Employee
 
WHILE  ( @LoopCounter IS NOT NULL
         AND  @LoopCounter <= @MaxEmployeeId)
BEGIN
   UPDATE TOP(1) #Employee
   SET  Status = 1, @EmployeeName = Name
   WHERE Id = @LoopCounter  AND Status = 0 
 
   PRINT @EmployeeName  
 
   SELECT @LoopCounter  = min(id) FROM #Employee  
   WHERE Id >= @LoopCounter AND Status = 0
END

RESULT:
Looping through table records Sql Server 10

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 #EmployeeCopy (LoopId INT IDENTITY(1,1), 
  Id INT, Name NVARCHAR(100), Status TINYINT)
--Copy data to the table with identity column
INSERT INTO #EmployeeCopy(Id, Name, Status)
SELECT Id, Name, Status FROM #Employee 

DECLARE @LoopCounter INT , @MaxEmployeeId INT, 
        @EmployeeName NVARCHAR(100)
SELECT @LoopCounter = min(LoopId),@MaxEmployeeId = max(LoopId) 
FROM #EmployeeCopy
WHILE ( @LoopCounter IS NOT NULL 
        AND  @LoopCounter <= @MaxEmployeeId)
BEGIN
   SELECT @EmployeeName = Name 
   FROM #EmployeeCopy  WHERE LoopId = @LoopCounter
   PRINT @EmployeeName	
   SELECT @LoopCounter  = min(LoopId) 
   FROM #EmployeeCopy  WHERE LoopId > @LoopCounter
END

RESULT:
Looping through table records Sql Server 11

In this article I have covered most of the basic scenarios which we across. If you have any other scenario and use different approach, post a comment I will update the article.

[ALSO READ] PRINT/SELECT Statement messages within WHILE LOOP or BATCH of statement is not displayed immediately after it’s execution- Sql Server