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.

Let's create the Products.

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

Let's 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

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.

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.

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 have 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.

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