WHILE LOOP (T-SQL)

This week I bring while loop which is very powerful tool to iterate through your actions generating data and objects doing that one step at time whit each iteration.
I will demonstrate just basic concepts here, but you can adapt these examples to satisfy your needs.


While loop code template:



Here is very basic structure which can be used like a template in your code:

DECLARE @counter INT

SET @counter = 1

WHILE @counter <= 5 --#5 is just an example

BEGIN
  

       /*

              YOUR CODE

       */

     

       SET @counter = @counter + 1

END

While loop in action:

The loop looks very simple, so let's put it in practice.

DECLARE @counter INT

SET @counter = 1

WHILE @counter <=5

BEGIN      

       PRINT @counter 

       IF(@counter = 5)

              PRINT 'THE END OF WHILE LOOP'

        /*

       Be careful by putting counter at the beginning of while loop

       Sometimes your code logic is different and your counter

       needs to be placed somewhere else in your loop

       */

       SET @counter = @counter + 1

END

And finally, our first result looks like a real beauty, we passed trhough all these steps using While loop:

1

2

3

4

5

THE END OF WHILE LOOP

While loop used for managing data and objects:

Also, while loop can be used for retrieving data (row by row or by set of rows), creating objects (tables, columns, ...) and deleting objects (tables, columns,...).
This is very good technique for managing data and objects, but be careful using it because it can slow down performances on your code rapidly handling with big portion of data and can cause permanent damages on your database objects (it it was used for deleting objects).

This is very simple example how to insert data into a table. The same logic can be used for getting data from any table though.

DECLARE @counter INT

SET @counter = 0

CREATE TABLE tempProducts (ProductID INT PRIMARY KEY, ProductName VARCHAR(255))

WHILE @counter <10

BEGIN

       /*

       Be careful by putting counter at the beginning of while loop

       Sometimes your code logic is different and your counter

       needs to be placed at the end or to some other spot inside of your loop

       */

       SET @counter = @counter + 1      

       --Get our results one by one using iteration counter

       PRINT 'Product: ' + CAST(@counter AS VARCHAR(10)) + ' is added.'

      

       INSERT INTO tempProducts(ProductID, ProductName)

       SELECT @counter AS ProductID, 'Product '+ CAST(@counter AS VARCHAR(255))

       IF(@counter = 10)

              PRINT 'THE END OF WHILE LOOP' 

END

SELECT * FROM tempProducts

DROP TABLE tempProducts

Here is the result:

ProductID ProductName

1 Product 1

2 Product 2

3 Product 3

4 Product 4

5 Product 5

6 Product 6

7 Product 7

8 Product 8

9 Product 9

10 Product 10

Example: Creating Movie Theater application

Let's create seats for our movie theater and get some popcorn after. Here are requirements:

• 160 seats
• Rows from 10 seats each
• First 10 rows are not available for booking (VIP guests) 

Here is a more complex code using the whole logic that we mentioned earlier: 

DECLARE @seatsFrom int, @seatsTo int, @seatsCounter int, @rowSize int, @loopCounter int

SET @seatsFrom = 10

SET @seatsTo = 160

SET @rowSize = 10

SET @loopCounter = 1

SET @seatsCounter = @seatsFrom

WHILE @seatsCounter < @seatsTo

BEGIN             

       PRINT 'ROW ' + CAST(@loopCounter AS VARCHAR(25))  + '--> Seats from: ' + CAST(@seatsCounter AS VARCHAR(25)) + ', Seats to: ' + CAST((@seatsCounter + @rowsize)-1 AS VARCHAR(25))

     

       SET @seatsCounter = @seatsCounter + @rowSize

       SET @seatsFrom = @seatsCounter

       SET @loopCounter = @loopCounter + 1

END

This looked like a pretty serious piece of code resulting in small application good enough to call all our friends for tonight's show:

ROW 1--> Seats from: 10, Seats to: 19

ROW 2--> Seats from: 20, Seats to: 29
ROW 3--> Seats from: 30, Seats to: 39
ROW 4--> Seats from: 40, Seats to: 49
ROW 5--> Seats from: 50, Seats to: 59
ROW 6--> Seats from: 60, Seats to: 69
ROW 7--> Seats from: 70, Seats to: 79
ROW 8--> Seats from: 80, Seats to: 89
ROW 9--> Seats from: 90, Seats to: 99
ROW 10--> Seats from: 100, Seats to: 109
ROW 11--> Seats from: 110, Seats to: 119
ROW 12--> Seats from: 120, Seats to: 129
ROW 13--> Seats from: 130, Seats to: 139
ROW 14--> Seats from: 140, Seats to: 149
ROW 15--> Seats from: 150, Seats to: 159

Mike C.