Using tempdb.. at the beginning didn't work for me either.
I agree with Borislav, though. You may want to use table variable instead. Though I don't think you can use it in the INTO TABLE clause.
>I am seeing the same
>
>This works: DROP TABLE #temp
>
>By object_ID isn't telling the truth.
>
>
>
>
>>object_id stopped working for me, I don't know why. I tried one batch and two batches and got completely confused. Looks like I can not drop the table this way (I was getting a permission error), but now the object_id just doesn't detect.
>>
>>I think I had similar troubles in the past.
>>
>>>Ok, I don't understand what's wrong here:
>>>
>>>
>>>This works:
>>>
>>>drop table #temp
>>>declare @tablename varchar(10)
>>>
>>>create table #temp (ch char(1))
>>>set @tablename = '#temp'
>>>
>>>if object_Id(@tablename) is not null
>>> exec ('drop table ' + @tablename)
>>>
>>>
>>>
>>>
>>>This does not work:
>>>
>>>DECLARE @sTableName varchar(max)
>>>
>>>SET @sTableName = '#temp'
>>>
>>>if object_Id(@sTableName) is not null
>>> exec ('drop table ' + @sTableName)
>>>
>>>SELECT t.iTrade_Id
>>> INTO #temp
>>> FROM Trade t
>>>
>>>
>>>
>>>
>>>
>>>
>>>>This is I just tried:
>>>>
>>>>drop table #temp
>>>>declare @tablename varchar(10)
>>>>
>>>>create table #temp (ch char(1))
>>>>set @tablename = '#temp'
>>>>
>>>>if object_Id(@tablename) is not null
>>>> exec ('drop table ' + @tablename)
>>>>
>>>>And it didn't work. Give me one sec, I think you need a variable for whole EXEC line.
>>>>
>>>>>Ok, this is not working:
>>>>>
>>>>>
>>>>>CREATE PROCEDURE spDropTable
>>>>> @sTableName VARCHAR(MAX)
>>>>>
>>>>>AS
>>>>>
>>>>> IF OBJECT_ID(@sTableName) IS NOT NULL
>>>>> EXEC('DROP TABLE ' + @sTableName)
>>>>>
>>>>>
>>>>>I'm calling it like this:
>>>>>
>>>>>
>>>>>
>>>>>EXEC spDropTable '#TmpNetAmt'
>>>>>
>>>>>SELECT t.iTrade_Id
>>>>> INTO #TmpNetAmt
>>>>> FROM Trade t
>>>>>
>>>>>
>>>>>I get "There is already an object named '#TmpNetAmt' in the database."
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>
declare @tablename varchar(10)
>>>>>>
>>>>>>create table #temp (ch char(1))
>>>>>>set @tablename = '#temp'
>>>>>>if object_Id(@tablename) is not null
>>>>>> drop table #temp
>>>>>>
>>>>>>>How do you do this when passing in a temp table name?
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>DECLARE @sTableName VARCHAR(MAX)
>>>>>>>
>>>>>>>if object_ID('N''#Stats') is not null
>>>>>>> drop table #Stats
>>>>>>>
>>>>>>>-- Where does the @sTableName go?
>>>>>>>-- This errors:
>>>>>>>
>>>>>>>if object_ID('N'@sTableName) is not null
>>>>>>> drop table #Stats
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>With temp tables you need to use different approach, object_Id. (from the private answer to me by Borislav)
>>>>>>>>
>>>>>>>>if object_ID('N''#Stats') is not null
>>>>>>>> drop table #Stats
>>>>>>>>
>>>>>>>>BTW, I would use slightly different syntax for your function.
>>>>>>>>
>>>>>>>>if exists (....)
>>>>>>>> set @myRetValue = 1
>>>>>>>>
>>>>>>>>>I wrote this function to test if a table exists:
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>CREATE FUNCTION dbo.fnTableExists
>>>>>>>>> (@sTableName VARCHAR(50))
>>>>>>>>>
>>>>>>>>>RETURNS TinyInt
>>>>>>>>>AS
>>>>>>>>>
>>>>>>>>> BEGIN
>>>>>>>>>
>>>>>>>>> DECLARE @bTableExists TINYINT
>>>>>>>>> SET @bTableExists = 0
>>>>>>>>>
>>>>>>>>> SELECT @bTableExists = 1
>>>>>>>>> WHERE EXISTS
>>>>>>>>> (SELECT Table_Name
>>>>>>>>> FROM INFORMATION_SCHEMA.TABLES
>>>>>>>>> WHERE LOWER(Table_Name) = LOWER(@sTableName))
>>>>>>>>>
>>>>>>>>> RETURN @bTableExists
>>>>>>>>>
>>>>>>>>> END
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>Then I tried this and the return value is 0:
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>SELECT t.iTrade_Id, t.decTrade_Ext_Price + t.decTrade_Shipping_Charge + t.decTrade_Other_Charge AS NetAmount
>>>>>>>>> INTO #TmpNetAmt
>>>>>>>>> FROM Trade t
>>>>>>>>>
>>>>>>>>>DECLARE @bTableExists TINYINT
>>>>>>>>>SELECT @bTableExists = dbo.fnTableExists('#TmpNetAmt')
>>>>>>>>>
>>>>>>>>>SELECT @bTableExists
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>However, DROP TABLE #TmpNetAmt works ok. The function works for regular tables.
>>>>>>>>>
>>>>>>>>>What am I doing wrong here?
If it's not broken, fix it until it is.
My Blog