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?