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 -- Returns 0
>>>>>>>>>

>>>>>>>>>
>>>>>>>>>However, DROP TABLE #TmpNetAmt works ok. The function works for regular tables.
>>>>>>>>>
>>>>>>>>>What am I doing wrong here?