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?