Using the SQL WHILE loop
-- Create a temporary table, note the IDENTITY
-- column that will be used to loop through
-- the rows of this table
CREATE TABLE #tmpTable (
RowID int IDENTITY(1, 1),
CustomerID int,
FirstName varchar(30),
LastName varchar(30)
)
DECLARE @NumberRecords int, @RowCount int
DECLARE @CustomerID int, @FirstName varchar(30), @LastName varchar(30)
-- Insert the resultset we want to loop through
-- into the temporary table
INSERT INTO #tmpTable (CustomerID, FirstName, LastName)
SELECT CustomerID, FirstName, LastName
-- Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1
-- loop through all records in the temporary table
-- using the WHILE loop construct
WHILE @RowCount <= @NumberRecords
BEGIN
--Do Processing
SET @RowCount = @RowCount + 1
END
-- drop the temporary table
DROP TABLE #tmpTable
