Importance of While Loop and Table Variable in SQL Server


The while loop works like in any other programming language. It is an entry controlled loop and it is used to iterate the results.

When we have tables with an identity column then it can be iterated.

Lets create the Products.

CREATE TABLE Products
(
    iProductID INT IDENTITY(1,1) PRIMARY KEY
   ,vProductName VARCHAR(100) NOT NULL
   ,vManfacturer  VARCHAR(100) NOT NULL
   ,LastUpdated DATETIME DEFAULT GETDATE()
)

Lets insert some records into the products table.

INSERT INTO Products(vProductName,vManfacturer) VALUES('Laptop','HCL')

INSERT INTO Products(vProductName,vManfacturer) VALUES('Refrigerator','Godrej')

INSERT INTO Products(vProductName,vManfacturer) VALUES('Television','Samsung')

INSERT INTO Products(vProductName,vManfacturer) VALUES('Laptop','HCL')

INSERT INTO Products(vProductName,vManfacturer) VALUES('Two Stroke Motor','Bajaj')


After several records are inserted, let's see all the records.

SELECT * FROM Products

WhileLoop1.jpg

Let's try to execute the while loop and iterate the table records one by one.

DECLARE @totalRecords INT
DECLARE @I INT

SELECT @I = 1
SELECT @totalRecords = COUNT(iProductID) FROM Products
WHILE (@I <= @totalRecords)
BEGIN
SELECT * FROM Products WHERE iproductID = @I
    SELECT @I = @I + 1
END


The above table has the identity column values in the sequence order. It can be iterated and you can see the results.


whileloop2.jpg


It has executed successfully and you can see all the records in the table.

Now i will remove one record in the table.

DELETE FROM Products WHERE iProductID = 3

Now I try to execute again the above loop.

whileloop3.jpg

You can see the results table is messed up. The third row does not exist, because we have deleted it so now the table is showing only 4 records. The total count is now 4. The loop was very helpful to execute the table row by row. But not now...

We have an alternate way to solve this issue. The table variable or temp table can be used now.

Let's see how to execute the table row by row now.

The table variable has to be created with an identity column and all the same columns from the products table has to be included.
The total number of records has to be taken from the table variable and the same procedure needs to be applied.


DECLARE @totalRecords INT
DECLARE @I INT

DECLARE @ProductTab TABLE
(
    SNO INT IDENTITY(1,1) PRIMARY KEY
   ,iProductID INT NOT NULL
   ,vProductName VARCHAR(100) NOT NULL
   ,vManfacturer  VARCHAR(100) NOT NULL
   ,LastUpdated DATETIME DEFAULT GETDATE()
)

INSERT INTO @ProductTab(iProductID,vProductName,vManfacturer,LastUpdated)
SELECT iProductID,vProductName,vManfacturer,LastUpdated FROM Products

SELECT @I = 1
SELECT @totalRecords = COUNT(iProductID) FROM @ProductTab
WHILE (@I <= @totalRecords)
BEGIN
SELECT * FROM @ProductTab WHERE SNO = @I
    SELECT @I = @I + 1 
END


The output of the same table is now as below.

WhileLoop4.jpg

Hope this will help you to use a while loop to iterate the table results set with the help of a table variable.

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now