7

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.

2
  • 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?
    – Dat Nguyen
    Aug 31, 2019 at 2:43
  • @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.
    – CB_Ron
    Sep 9, 2019 at 22:10

2 Answers 2

12

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 . . .
3
  • 1
    And add IsDeleted = 0 to the query that populates the cursor. Or IsDeleted IS NULL or <> 1 or whatever logic you need. Aug 19, 2019 at 17:20
  • Beautiful - that worked!
    – CB_Ron
    Aug 19, 2019 at 17:55
  • @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.
    – CB_Ron
    Aug 19, 2019 at 17:57
9

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

.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.