SQL Server provide WHILE as looping statement in SQL Server. It is similar with WHILE loop in C# or object oriented programing language.

WHILE execute the code block till the condition is satisfied, once condition is not satisfied then it exist the loop.
Below image show the execution cycle of the WHILE loop. First we need to declare the variable to execute the loop. WHILE loop check the condition if condition is satisfied the statements block get execute else it will end the loop. Every time when condition get satisfied then it increment value.


While loop execution process

Syntax for WHILE loop

   DECLARE @CounterVariable INT = 1
   WHILE < condition 1>
   BEGIN
   
< block of statement 1>
< block of statement 2>

< increment counter variable value>
   
   END

1) Example

Simple WHILE loop example, In below example we will print 1 to 5 numbers using WHILE loop.

Example

   DECLARE @i INT = 1 --Declare counter variable value
   WHILE @i <= 5 --Check Condition
   BEGIN
   
PRINT @i --Print statement

SET @i = (@i + 1) --increment counter variable value by 1
   
   END

Output

while loop simple example


Note : If we not increment value of counter variable then WHILE loop execute infinite time.


2) Example

WHILE loop example using break statement.

In below example we try to execute the WHILE loop example till 10, but we use the break statement on if counter variable value reach on 6.


Example

   DECLARE @i INT = 1
   WHILE @i <= 10
   BEGIN
   
PRINT @i

IF @i = 6
BEGIN
    BREAK --Break Loop
END

SET @i = (@i + 1)
   
   END

Output

WHILE loop with break statement example


Note : In SQL server we use the break statement to break the WHILE loop.


3) Example

use WHILE loop on SQL table.

Below is the result set of employee, print / select only those employee who belong from Delhi.


result set for if condition

Example

DECLARE @i INT = 1
DECLARE @max INT = ( SELECT COUNT (*) FROM tblEmployee) --get no.of employee from table
WHILE @i <= @max
BEGIN

DECLARE @EmpName VARCHAR(10) = '', @EmpCity VARCHAR(10) = ''

SELECT @EmpName = EmpName, @EmpCity = EmpCity FROM tblEmployee WHERE Id = @i

IF @EmpCity = 'Delhi' --Break Loop
BEGIN
    SELECT @i , @EmpName , @EmpCity
END

SET @i = (@i + 1)
   
   END

Output

use while loop on sql table, retrive data from table using loop