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
>
>
>However, DROP TABLE #TmpNetAmt works ok. The function works for regular tables.
>
>What am I doing wrong here?
--sb--