3

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Simon Rigby
  • 1,786
  • 4
  • 17
  • 28
  • 1
    Can you show us what you're doing?? The **best** option would be to eliminate the cursor altogether - which is possible in at least 90% of all cases... – marc_s Oct 05 '11 at 15:46
  • Thanks and yes you're right .. and I'm still not convinced the cursor is needed but was more interested in knowing that it was possible. – Simon Rigby Oct 06 '11 at 09:00

2 Answers2

2

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.

Simon Rigby
  • 1,786
  • 4
  • 17
  • 28
  • The table in my answer doesn't have a PK and reflects the results of the `DELETE` correctly. – Martin Smith Oct 06 '11 at 09:02
  • Hi .. yes I was able to do a proof of concept without the PK and it worked correctly. I don't know why in the other case it requires it. – Simon Rigby Oct 06 '11 at 09:03
  • Did you have some other cursor options set? – Martin Smith Oct 06 '11 at 09:05
  • Hi martin .. no .. no cursor options .. defaults all the way. I;m not convinced this is the answer which is why I've not marked it. – Simon Rigby Oct 26 '11 at 14:59
  • 1
    Think I've found the explanation. You need a dynamic cursor to have the changes in the source table reflected in the cursor fetch. You were getting an [implicit conversion](http://msdn.microsoft.com/en-us/library/ms190641.aspx) to a `keyset` or `static` cursor. Possibly as you were ordering by an unindexed column. Is there an `ORDER BY` in your query on the PK column? I get the same behaviour in the query in my answer if I add an `ORDER BY C` to the `SELECT` – Martin Smith Oct 29 '11 at 21:33
0

Not massively familiar with cursors but from a quick test this end you need to avoid declaring the cursor with the STATIC or KEYSET options then the changes to the underlying table are reflected in the cursor.

SET NOCOUNT ON;

DECLARE @WorkingTable TABLE(C int)

INSERT INTO @WorkingTable VALUES (1),(2),(3)

DECLARE @C int

DECLARE wt_cursor CURSOR 
DYNAMIC /*Or left blank but not STATIC or KEYSET*/
FOR 
SELECT C
FROM @WorkingTable

OPEN wt_cursor;

FETCH NEXT FROM wt_cursor 
INTO @C

DELETE FROM @WorkingTable

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @C;


    FETCH NEXT FROM wt_cursor 
    INTO @C;
END
CLOSE wt_cursor;
DEALLOCATE wt_cursor;
Martin Smith
  • 438,706
  • 87
  • 741
  • 845