DELETE WHERE CURRENT OF with SQL2000

Hi,

Can someone please explain to me why this SQL doesn't give me an empty resultset, I receive 299 rows from id=701 and up. And is there a workaround?

I'm using SQL 2000 sp2, I also tried it without sp. But it works with SQL 6.5 and 7.

----------------------------------

SET NOCOUNT ON

CREATE TABLE #mytable (id int NOT NULL,txt varchar(255))

DECLARE @i int
SELECT @i=1
WHILE @i < 1000 BEGIN
  INSERT INTO #mytable VALUES (@i,'AAAAA')
  SELECT @i=@i+1
END

DECLARE @id int

DECLARE mycursor CURSOR
  FOR SELECT id FROM #mytable
  FOR UPDATE

OPEN mycursor
FETCH NEXT FROM mycursor INTO @id

WHILE @@FETCH_STATUS<>-1 BEGIN
  DELETE FROM #mytable WHERE CURRENT OF mycursor
  FETCH NEXT FROM mycursor INTO @id
END

CLOSE mycursor
DEALLOCATE mycursor

SELECT * from #mytable

DROP TABLE #mytable
GO

----------------------------------

Thanks,
pivar
LVL 22
pivarAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
change the temp table to have a primary key:

CREATE TABLE #mytable (id int NOT NULL,txt varchar(255), primary key(id))

and it will work (again)

CHeers
0
 
pivarAuthor Commented:
Thanks,

That did it. Do you know if this is by design or is it a bug? Are MS likely to correct this behaviour?

/pivar
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I guess this is "by design". In fact, when you start deleting using a cursor, the physical emplacement of the data can change, and thus the WHERE CURRENT OF will actually fail to find the row, but won't say anything because it simply works like a normal where clause internally (-> no matches found).

I don't think they will change this behaviour...

Of course, you shall not use cursors whenever possible, because they are very bad in performance, and you can almost always simulate by using TSQL

CHeers
0
 
pivarAuthor Commented:
Thanks,

That did it. Do you know if this is by design or is it a bug? Are MS likely to correct this behaviour?

/pivar
0
 
pivarAuthor Commented:
Ooops, it's not wise to refresh the inputpage to see if theres any answers... 8-)

Yes, actually I think you and I had this conversation about cursors before. And I agree with you, but I have this cursor I don't think I can do without. I'm doing the delete if another procedure returns a certain result. This involves several selects and other checks. Also I have to use the same functionality in several different selects, so I don't want to have to update codechanges at several locations. (I've to check r/w rights on a row-by-row basis for several SELECTs and TRIGGERs). If I could do this another way I would gladly do that. This is of course timeconsuming.

/pivar
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.