I have a SQL Server 2005 cursor operating over a table variable called @workingSet.
Some times rows can be related and in this case I process the row I have fetched and the related rows at the same time. I then remove the related records from @workingset as I don't need to process then in the loop.
In a @workingSet with 7 rows, the first two are related so when I process 1 I also process 2. I remove row 2 from the cursor source (@workingSet) and then fetch next. The problem is it returns the second row in @workingset (the one I deleted on the previous iteration).
I was of the impression that this could be done i.e. deleting an item from a source that a cursor operates on and it will honour the delete in subsequent fetches.
The answer appears to be that the table variable that is being used as the source of the cursor needs to have a primary key. I've added this and all works correctly.