fbpx
image9

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;