Not able to delete a temp table

brgdotnet
brgdotnet used Ask the Experts™
on
I have sql that will check if a temp table exists. If the table does exist I need to delete it(Which I suppose means to drop it), because immediately after deleting it, I want to recreate it. The code is not working, as I keep getting an error message: "There is already an object named #TempTable in the database. Can someone tell me what is wrong with the code?

IF object_id('#TempTable') is not null
BEGIN
  DROP TABLE #TempTable
END

CREATE TABLE #TempTable
(Id varchar(50),
 NAME varchar(50))
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Software Team Lead
Commented:
try:
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
    DROP TABLE #TempTable

CREATE TABLE #TempTable
(Id varchar(50),
 NAME varchar(50))

Open in new window

Mike EghtebasDatabase and Application Developer
Commented: 
IF object_id('tempdb.dbo.#TempTable', 'U') is not null
  DROP TABLE #TempTable

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial