ForEach loop in SQL

Have you ever needed to do a ForEach loop in MSSQL? Ever wanted to be able to loop through all results of a query and perform an action? No? I needed to the other day, and it was a PAIN to figure out. So, once I did, I figured I need to document it for others who may be just as stumped.

Before I explain what I found, a quick background of the problem and the structure of the DB would be helpful.

Customer A wants to update each Contract's Remarks field with the combined remarks from all the Contract's Equipment location.

Here's the problem. Each Contract table is linked to a ContractDetails table (one to many). Each ContractDetails table is linked to the Equipments table (one to one). Since a basic update statement with an inner join only updates the contracts based on the first result of the equipment, that won't work. We could potentially have hundreds of equipments tied to each contract.


DECLARE @ContractID INT
DECLARE @Equip Varchar (500)
DECLARE @Location VARCHAR (1024)

DECLARE cur CURSOR FOR
select cd.ContractID, e.equipmentnumber, isnull(e.Location, '')[equipment location]
from contractdetails cd
inner join equipments e on cd.equipmentid = e.equipmentid
order by cd.ContractID

OPEN cur

FETCH NEXT FROM cur INTO @ContractID, @equip, @Location
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Location <> ''
UPDATE Contracts SET Remarks = LEFT(Remarks + char(13) + char(10) + 'Equipment number ' + @Equip + ' is located at ' + @Location + '.', 1024)
WHERE ContractID = @ContractID

FETCH NEXT FROM cur INTO @ContractID, @equip, @Location
END

close cur
deallocate cur


We needed to declare a cursor. The cursor is a collection of results from the select statement (in this case: select cd.ContractID, e.equipmentnumber, isnull(e.Location, '')[equipment location]
from contractdetails cd
inner join equipments e on cd.equipmentid = e.equipmentid
order by cd.ContractID). Once that was done, we open the cur, and Fetch each of the results, one by one, peforming the action (in our case, the update statement) for each result.