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;