SQL Server – Loop through an existing table

In T-SQL you may want to loop through an existing table and do something. You may want want to use this type of processing in a SSRS report.

Here is an example of walking through a table that simply extracts a person’s first, last, and middle name. I didn’t do anything after the read, but simply added a display that shows the current row in the process. I will post an updated version that shows how to break down a column that is delimited and insert each section into its own column in another table.

Here is the code:

DECLARE @maxrows INT
DECLARE @rowNum INT
DECLARE @NameAddressGUID uniqueidentifier
DECLARE @LastName varchar(50)
DECLARE @FirstName varchar(50)
DECLARE @MiddleName varchar(50)

SELECT @maxRows = COUNT(*)
FROM NameAddress

SELECT @maxrows

SELECT TOP 1 @NameAddressGUID = NameAddress_GUID,
             @LastName = LName,
             @FirstName = FName,
             @MiddleName = MName
FROM NameAddress
ORDER BY NameAddress_GUID ASC

SET @rowNum = 0

-- Loop until last row is reached
WHILE @rowNum < @maxRows
BEGIN
SET @rowNum = @rowNum + 1

--This is where you can now do something to the record
SELECT 'YOU ARE ON ROW ', @rowNum

--Read the next row making sure the ID of the next row
--is greater than previous row
SELECT TOP 1 @NameAddressGUID = NameAddress_GUID,
             @LastName = LName,
             @FirstName = FName,
             @MiddleName = MName
FROM NameAddress
WHERE NameAddress_GUID > @NameAddressGUID
ORDER BY NameAddress_GUID ASC

END