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:
01328357
Views:
9
How do you pass the parameter to Object_ID when the table name is being passed in?
CREATE PROCEDURE spDropTable
    @sTableName VARCHAR(MAX)

AS

-- How does @sTableName fit in here?

    IF object_id('tempdb..#TmpNetAmt') IS NOT NULL
        DROP TABLE #TmpNetAmt
>The temp tables are not presented in INFORMATION_SCHEMA.TABLES view. You can use object_id for all tables
>
>IF object_id('tempdb..#TmpNetAmt') IS NOT NULL
>	DROP TABLE #TmpNetAmt
>
>
>>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