I had a challenge yesterday that I would normally solve using some Visual Basic code but had to do it in pure SQL on a SQL 2005 server.
THE CHALLENGE:
Loop through all the records in a table and then update the table based on some logic or condition.
THE SOLUTION:
Using a SQL Cursor, I was able to loop through all the records in a table and then run an SQL update command for certain records that matched a particular criteria.
SQL Sample Code
DECLARE @myEmpID int
DECLARE MyCursor CURSOR FOR
SELECT DISTINCT EmployeeID FROM Employees WHERE Company=64
OPEN myCursor
--Read the initial emploee id value from the cursor
FETCH NEXT FROM myCursor
INTO @myEmpID
WHILE @@FETCH_STATUS = 0
BEGIN
--Update goes here
UPDATE Employees
SET [Status] = 'T'
WHERE EmployeeID = @myEmpID AND Company=54
FETCH NEXT FROM MyCursor
INTO @myEmpID
END
CLOSE MyCursor
