A little reminder of how to use while loop with SQL. End because everytime I forgot how to use it in SQL, I wanted to mark the occasion pour myself 👩💻
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 of how to use while loop with SQL
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. Why use stored procedure in SQL than queries ?
If you want to support me, you can always buy me a coffee.
Any question or comment ? Express yourself in the section down below, it would be a pleasure to answer them ✨
Have a great day!