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]()
