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

because they are created in TempDb, not in your regular database.

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?