Infinite loop in CURSOR 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 &lt;&gt; -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.
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 . . .