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?