Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Function To Test For Table Doesn't Work
Message
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
01328338
Message ID:
01328347
Views:
9
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?
Everything makes sense in someone's mind
public class SystemCrasher :ICrashable
In addition, an integer field is not for irrational people
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform