In this article I will explain with an example, how to use WHILE LOOP in SQL Server (Transact-SQL).
The WHILE statement as used in various programming languages works in similar way in SQL Server. It is a control flow statement and it is used to perform iterations until the condition holds TRUE.
 
 
Syntax
Following is the syntax of WHILE LOOP in SQL Server. It consists of a Boolean Expression which needs to be TRUE in order to execute the WHILE LOOP.
The Statements to be executed will lie between the BEGIN and END keywords.
WHILE<Boolean Expression> 
BEGIN 
   <Statements to be executed> 
END
 
 

SIMPLE WHILE LOOP
Example
In the following example, the name Mudassar Khan will be printed until the @Count variable value is less than 10.
DECLARE @Count INT =0
WHILE @Count < 10
BEGIN 
   PRINT 'Mudassar Khan'
   SET @Count = @Count + 1
END
 
Output
In the following example, the name Mudassar Khan will be printed until the @Count variable value is less than 10.
Using WHILE LOOP in SQL Server
 
 
BREAK statement in WHILE LOOP
Example
In the following example, the name Mudassar Khan will be printed until the @Count variable value is less than 10. But here, when the @Count variable value is 7, then the WHILE LOOP is stopped using the BREAK statement.
DECLARE @Count INT =0
WHILE @Count < 10
BEGIN 
   IF @Count = 7
   BEGIN
        PRINT 'STOPPED'
        BREAK
   END
   PRINT 'Mudassar Khan'
   SET @Count = @Count + 1
END
 
Output
In the following example, the name Mudassar Khan will be printed until the @Count variable value is not equal to 7.
Using WHILE LOOP in SQL Server
 
 
CONTINUE statement in WHILE LOOP
Example
In the following example, the name Mudassar Khan will never be printed as the CONTINUE statement ignores the statements after it.
DECLARE @Count INT =0
WHILE @Count < 10
BEGIN 
   PRINT 'ASPSNIPPETS'
   SET @Count = @Count + 1
   CONTINUE
   PRINT 'Mudassar Khan'
END
 
Output
In the following example, the name Mudassar Khan will never be printed due to the CONTINUE statement.
Using WHILE LOOP in SQL Server