
Posted on June 11, 2019 by Team CodeForHost
How to get the dynamic values in SQL for while loop?
SQL for while loop:
You just want to update all the empAddress records where there’s a corresponding employee record, right? You shouldn’t need a loop for that:
UPDATE empAddress SET thumbID = NEWID()
FROM empAddress
INNER JOIN employees ON employees.empID = empAddress.empID
Or, assuming every empAddress has an empID, just:
UPDATE empAddress SET thumbID = NEWID()
The looping way to do it in SQL would be to use a cursor, but watch how many people yell at me for even mentioning that cursors exist. (They’re inefficient, but good to know about, IMO.) This is just to demonstrate that such a thing is possible, but totally unnecessary in this case.
DECLARE cur CURSOR FOR
SELECT empID FROM employees
DECLARE @currentEmpID int
OPEN cur
FETCH NEXT FROM cur
INTO @currentEmpID
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE empAddress
SET thumbID = NEWID()
WHERE empID = @currentEmpID
FETCH NEXT FROM cur
INTO @currentEmpID
END
CLOSE cur;
DEALLOCATE cur;