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...