Infinite loop in CURSOR

Question

I'm trying to use a cursor to clean up temp tables when they are no longer needed. I have a small table which has the names of the temp tables along with an identifier. The cursor is stuck in an infinite loop, but only if I execute certain statements in it. If I just print out the values from the FETCH, it works perfectly. Here is the code.

DECLARE @id bigint;
DECLARE @table_name varchar(max);
DECLARE st CURSOR LOCAL FAST_FORWARD FOR 
SELECT ID, TableName FROM SearchTables WHERE CustomerID IS NULL
OPEN st
FETCH NEXT FROM st INTO @id, @table_name
WHILE @@FETCH_STATUS <> -1 
BEGIN 
 IF(OBJECT_ID(@table_name) IS NOT NULL) 
 EXEC('DROP TABLE ' + @table_name);
 UPDATE SearchTables SET Deleted=1 WHERE ID=@id;
 PRINT CAST(@id AS varchar(max)) + ' ' + @table_name;
 FETCH NEXT FROM st INTO @id, @table_name;
END 
CLOSE st
DEALLOCATE st

If I comment out these lines

 IF(OBJECT_ID(@table_name) IS NOT NULL) 
 EXEC('DROP TABLE ' + @table_name);
 UPDATE SearchTables SET Deleted=1 WHERE ID=@id;

PRINT outputs all of the IDs and table names. If I don't comment them, all I get is the first row over and over until I cancel the query. I also tried changing the IF line to EXEC('DROP TABLE IF EXISTS ' + @table_name) but that didn't work either.

• Dat Nguyen: I wonder why you did have a need to clean up the temp tables? Even it's local or global temp table, it's alive within a user transaction, and will be dropped automatically. I'm not sure for your case, so?
• CB_Ron: @DatNguyen I am not using local or global temp tables, I am using "permanent" temp tables. Which don't get dropped until tempdb is restarted.

Answer 1

Probably you're moving the row when you set Deleted=1 and reading it again with your FAST_FORWARD cursor. Use a STATIC cursor instead, which will iterate a copy of the data, and avoid mutating the data structure you are traversing.

DECLARE st CURSOR LOCAL STATIC FOR . . .

• Aaron Bertrand: And add IsDeleted = 0 to the query that populates the cursor. Or IsDeleted IS NULL or <> 1 or whatever logic you need.
• CB_Ron: Beautiful - that worked!
• CB_Ron: @AaronBertrand yes that is going to be part of the finished product. I had added the Deleted and CustomerID columns to SearchTables after some data had already been added. I just wanted to get rid of those tables first. The finished version will go into an Agent job.

Answer 2

You want WHILE @@FETCH_STATUS = 0 which means continue unless something isn't right.

Using <> -1 means it will continue even if the row fetched was missing, or it's not performing a fetch operation, making it infinite unless you get -1 as a return value, since there are 4 return values for @@FETCH_STATUS.

 0 The FETCH statement was successful.
-1 The FETCH statement failed or the row was beyond the result set.
-2 The row fetched is missing.
-9 The cursor is not performing a fetch operation

.