Use of the While Loop with SQL

A little reminder of how to use while loop with SQL.

Basic structure

DECLARE @Count INT
SET @Count = 0

WHILE(@Count < 11) BEGIN
  PRINT 'I have walked ' + CONVERT(VARCHAR, @Count) + ' meters today'
  SET @Count = @Count + 1
END

The output is

I have walked 0 meters today
I have walked 1 meters today
I have walked 2 meters today
I have walked 3 meters today
I have walked 4 meters today
I have walked 5 meters today
I have walked 6 meters today
I have walked 7 meters today
I have walked 8 meters today
I have walked 9 meters today
I have walked 10 meters today

Using BREAK statement

DECLARE @Count INT
SET @Count = 0

WHILE(@Count < 11) BEGIN
  PRINT 'I have walked ' + CONVERT(VARCHAR, @Count) + ' meters today'
  
  IF @Count >= 7 BEGIN
    PRINT 'Finally, 7 meters is enough for today !'
    BREAK
  END

  SET @Count = @Count + 1
END

The output is

I have walked 0 meters today
I have walked 1 meters today
I have walked 2 meters today
I have walked 3 meters today
I have walked 4 meters today
I have walked 5 meters today
I have walked 6 meters today
I have walked 7 meters today
Finally, 7 meters is enough for today !

Using CONTINUE statement

DECLARE @Count INT
SET @Count = 0

PRINT 'Go get the multipliers of 3.'
WHILE(@Count < 11) BEGIN
  IF @Count % 3 > 0 BEGIN
    SET @Count = @Count + 1
    CONTINUE -- If @count is not a multiplier of 3, the loop continues
  END

  PRINT CONVERT(VARCHAR, @Count) + ' is a multiplier of 3.'

  SET @Count = @Count + 1
END

The output is
Go get the multipliers of 3.
0 is a multiplier of 3.
3 is a multiplier of 3.
6 is a multiplier of 3.
9 is a multiplier of 3.

The While loop and tables

Let's create a table that contains all the multipliers of 9.

Feed the table

DECLARE @Count INT
SET @Count = 1

DECLARE @Multipliers_nine TABLE 
(Id INT PRIMARY KEY IDENTITY(1,1), 
Value INT)

WHILE(@Count < 100) BEGIN
  IF @Count % 9 > 0 BEGIN
    SET @Count = @Count + 1
    CONTINUE -- If @count is not a multiplier of 9, the loop continues
  END

  -- Insert in the table @Multipliers_nine
  INSERT INTO @Multipliers_nine
  VALUES (@Count)

  SET @Count = @Count + 1
END

SELECT * FROM @Multipliers_nine

The output is

Get table values with the While Loop

DECLARE @Index INT, @MaxIndex INT, @Multiplier INT
SELECT @Index = min(Id), @MaxIndex = max(Id)
FROM @Multipliers_nine

WHILE(@Index IS NOT NULL AND @Index <= @MaxIndex) BEGIN
    SELECT @Multiplier = Value
    FROM @Multipliers_nine Where Id = @Index

    PRINT '9 x ' + CONVERT(VARCHAR, @Index) + ' = ' + CONVERT(VARCHAR, @Multiplier)
    SET @Index = @Index + 1
END

The output is

9 x 1 = 9
9 x 2 = 18
9 x 3 = 27
9 x 4 = 36
9 x 5 = 45
9 x 6 = 54
9 x 7 = 63
9 x 8 = 72
9 x 9 = 81
9 x 10 = 90
9 x 11 = 99

Conclusion

This is really easy to use this structure in SQL and way faster to in term of table-manipulation. Prefer SQL over your programming language to manipulate a big range of data in tables.

Have a great day!