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?