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?