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!