[dba-SQLServer]something about temp tables...

There is no need to check for existence of a temp table unless it's
global (##tmp).  Nor there is a need to DROP temp tables because they do
get destroyed upon implicit or explicit RETURN from a stored procedure,
or upon closing of the connection that created them (like QA session).
The reason you're getting an error is because the optimizer is
validating every referenced object.  It does not do it in accordance
with procedural logic, following IF's and ELSE's.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Francisco H Tapia
Sent: Friday, August 29, 2003 2:44 PM
To: dba-SQLServer at databaseadvisors.com; SQL Server 2k List
Subject: [dba-SQLServer]something about temp tables...


I have the following similar statement in a tsql procedure that errors 
out telling me that there already is a #tmp object in the database... if

I read the TSQL correctly, the #TMP does not acctually get created on 
the ELSE statement if the 1=1 statement is true.  So why the error? 
Thanks in advance....

IF 1= 1 BEGIN
      IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = 
OBJECT_ID(N'tempdb..#tmp')) BEGIN
	DROP TABLE #tmp
END

	CREATE TABLE #tmp(tID INT IDENTITY (1,1) NOT NULL,Hcode
VARCHAR(50))
      END
ELSE BEGIN
      IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = 
OBJECT_ID(N'tempdb..#tmp')) BEGIN
	DROP TABLE #tmp
       END
	CREATE TABLE #tmp(tID INT IDENTITY (1,1) NOT NULL,Hcode
VARCHAR(50)) END



-- 
-Francisco

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com