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

Thanks Robert... I found a possible workaround :( tho not what I wanted...

http://support.microsoft.com/default.aspx?scid=kb;en-us;295305


only reason I check for the existance of #temp tables is in case I ever 
plan to nest the procedure and I end up using the same #name... thus the 
check.. tho that will problably bring on a whole new set of problems :)

-- 
-Francisco

Djabarov, Robert wrote:

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