Delete row from cursor source SQL Server

Question

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.

Answer 1

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.