There is already an object named '##Temp' in the database

Question

I have a stored procedure on SQL Server 2000. It contains:
select ... into ##Temp ...
...
drop table ##Temp

When I run the stored procedure with ADO a second time, it prompts:
There is already an object named '##Temp' in the database.
Could anyone kindly tell me what's wrong?

Answer 1

You should re-write your stored proc to drop the temp table if it exists, then you won't ever have this issue

IF (SELECT object_id('TempDB..##Temp')) IS NOT NULL
BEGIN
    DROP TABLE ##Temp
END