SQL Server - Loop Statement

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.

1) Example

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

Example

Copy

   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

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

Copy

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

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

SET @i = (@i + 1)
   
   END

Output

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.

Example

Copy

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