I have been asked to look into why a stored procedure is failing.  The problem is that the application gets an error that a temp table doesn't exist.  Here is the code with the problem (my comments in blue, contents were changed to protect company information):

       FETCH NEXT FROM @cursor
        INTO @ID, @Name
        
        IF @@FETCH_STATUS <> 0  This doesn't make sense, nor does the next WHILE statement.  It says if there are records in the cursor (which is SELECT * FROM a temp table), drop the temp table, get rid of the cursor, raise an error that something couldn't be found.  BUT IT WAS FOUND because there are records in the temp table.
        BEGIN
                DROP TABLE #tmp
                CLOSE @cursor
                DEALLOCATE @cursor
                RAISERROR('Raise Some Error',10,1,'LOG')
                RETURN 2
        END
        
        
        WHILE @@FETCH_STATUS = 0        This says if there aren't any records in the temp table, go to the next record in the temp table.  BUT THERE AREN'T ANY!
        BEGIN
        
                SELECT @SomeString = @SomeString + ','
                
                FETCH NEXT FROM @cursor
                INTO @ID, @Name
        
        END
        
        DROP TABLE #tmp  This is where the app is having problems.  It should check to see if the table exists first.  The table is getting dropped above, so this statement will never work with the current logic problems.
        CLOSE @cursor
        DEALLOCATE @cursor


The WHILE statements should be reversed.  The first one should be WHILE @@FETCH_STATUS = 0, and the second one should be WHILE @@FETCH_STATUS <> 0.  If there was ever a time where the temp table didn't have any records, the stored procedure would go into an infinite loop.  Also, the other records in the temp table never get processed since it drops the temp table before doing anything with them.

Now I didn't write the stored procedure but need to fix it.  Just complaining...