My question is why the update of SomeTable.OrderKey within the loop can affect the order in which MyCursor records are delivered by FETCH NEXT.
Experimentation seems to show that under some circumstances, records can jump around in the FETCH NEXT order as a result of the UPDATE command and can be processed multiple times within the loop. I wouldn't have expected updates made directly to the table would make it into the cursor while the procedure was running, but apparently this is what is happening.
DECLARE @OrderKeyValue decimal(10,4)
DECLARE @OrderKeyField decimal(10,4)
SET @OrderKeyValue = 0.0001
DECLARE MyCursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT OrderKey FROM SomeTable ORDER BY OrderKey
FOR UPDATE OF OrderKey
OPEN MyCursor
FETCH NEXT FROM MyCursor
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE SomeTable SET OrderKey = @OrderKeyValue WHERE CURRENT OF MyCursor
SET @OrderKeyValue = @OrderKeyValue + 1
FETCH NEXT FROM MyCursor INTO @OrderKeyField
END
CLOSE MyCursor
DEALLOCATE MyCursor