DELETE FROM WHERE CURRENT OF doesn’t work

… within a FETCH loop, which is not entirely surprising.

We all know, if we have a list
List<int> list = new List<int>(10);
for (int i = 0; i < 10; i++) { list.Add(i); }
and want to delete items like this
foreach (int i in list)
{
list.Remove(i);
}
utter mayhem will ensue, since the IEnumerator doesn’t tolerate the list mutation.

From our Visual Basic 6.0 and earlier experience we should however know that not all FOR EACH are created equal and may, instead of calling the programmer a moron, generate a random pattern of deleted entries, finally leaving more than zero items in the supposed-to-be-deleted list.

I just had a dejá vu about this when trying to maintain a list of work within a T-SQL stored procedure on SQL Server 2005 (Express, but that shouldn’t matter).

Temporary tables are so last millenium that nowadays everybody loves table variables (collections for the Oracle user) which in T-SQL can be created like this
DECLARE @work_items table(
[name] sysname NOT NULL,
[value] sql_variant NULL
)
And it’s not a bad idea to populate this table and then use a cursor to go through it and perform some action.
It may be also appropriate to remove the items that have been properly processed.
The meat of this would look like this
DECLARE wi_cursor CURSOR LOCAL FORWARD_ONLY
FOR
SELECT name, value FROM @work_items
FOR UPDATE

DECLARE @name sysname, @value sql_variant

OPEN wi_cursor
FETCH NEXT FROM wi_cursor INTO @name, @value

WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM @work_items WHERE CURRENT OF wi_cursor
FETCH NEXT FROM wi_cursor INTO @name, @value
END

CLOSE wi_cursor
DEALLOCATE wi_cursor
This has a strange resemblance to the mutating collection problem.

And it’s no surprise that it isn’t working either. Although, this better had been documented in SQL Books Online.

Only about half of the items are actually deleted, indicating the cursor is affected by the DELETE operation.

Funny side-note: The FOR UPDATE does not influence the behavior. It works (i.e. doesn’t raise an error) without.

Workaround:
Introduce a primary key and select it in the cursor’s select statement.
DECLARE @work_items table(
id int IDENTITY NOT NULL,
[name] sysname NOT NULL,
[value] sql_variant NULL,
PRIMARY KEY (id)
)


DECLARE wi_cursor CURSOR LOCAL FORWARD_ONLY
FOR
SELECT id, name, value FROM @work_items
FOR UPDATE

DECLARE @id int, @name sysname, @value sql_variant

OPEN wi_cursor
FETCH NEXT FROM wi_cursor INTO @id, @name, @value

WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM @work_items WHERE CURRENT OF wi_cursor
FETCH NEXT FROM wi_cursor INTO @id, @name, @value
END

CLOSE wi_cursor
DEALLOCATE wi_cursor

Odd, isn’t it? My guess: WHERE CURRENT OF is being translated to WHERE id = @id.

So you better remember to have the primary key in the cursor.

BTW: The Windows Live Spaces editor is still a royal pain when it comes to code snippets.

Advertisements
This entry was posted in Coding Horror. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s