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 >>
>>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 >> >>>>
>> >>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 >>>>