How to use WHILE Loop in Sql Server

Below is a simple demonstration of how to use while loop. The result will print the numbers from 0 to 100.

DECLARE @mn INT
DECLARE @mx INT
DECLARE @tblTemp TABLE(Number int)

SET @mn=0
SET @mx=100

WHILE( @mn <= @mx )
BEGIN
insert into @tblTemp
select @mn
SET @mn=@mn + 1
END

SELECT * FROM @tblTemp

code has two integer variables @mn and @mx respectively set to 0 and 100 initially. A temporary table has been created with one integer column. Inside the while loop @mn variable is incremented one by one until it reaches the maximum (100) and added to the temporary table.

Output:

Number
0
1
2
3
4
....
...
..