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?