Use of the While Loop with SQL

[lauweded]

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!

Comments

[rishi556]

Is there a reason we'd ever use this right inside SQL rather than processing it elswhere?

[lauweded]

For example, I used it to create recurrent events inside a Store Procedure. Instead of doing 20 requests from your app to your database for every recurring event, you will have only one request to your database, because this is the SQL that will do all the job; the loop and insert data in the table. The time your user gain creating a recurrent event is game-changing!
Sorry for my English, I hope I answered well to your question 🌻Hey @rishi556 ! It is a really good question. I prefer to do all the work possible inside SQL because you will optimize the speed of your program and reduce the number of database calls.

[hivebuzz]

Congratulations @lauweded! You have completed the following achievement on the Hive blockchain and have been rewarded with new badge(s) :

You received more than 200 upvotes. Your next target is to reach 300 upvotes.

_{You can view your badges on your board and compare yourself to others in the Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP}

Support the HiveBuzz project. Vote for our proposal!