Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links



Search this Blog


Post Categories


My gripe today

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.
                DROP TABLE #tmp
                CLOSE @cursor
                DEALLOCATE @cursor
                RAISERROR('Raise Some Error',10,1,'LOG')
                RETURN 2
        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!
                SELECT @SomeString = @SomeString + ','
                FETCH NEXT FROM @cursor
                INTO @ID, @Name
        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...

Print | posted on Tuesday, September 30, 2003 12:18 PM | Filed Under [ Gripes ]



# re: My gripe today

.....and 2 years years, (nearly to the day), i stumble across your meassage....

No answer though, sorry. Just thought I'd say I agree with you. I was looking at exactly the same example a few weeks ago and thought exactly the same thing. Ho hum.
9/12/2005 10:04 PM | jw
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET