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
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 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.
Hope this will help you to use a while loop to iterate the table results set with the help of a table variable.