Posted by Erland Sommarskog on 08/30/06 22:22

M Bourgon (bourgon@gmail.com) writes:
> I have two SPs, call them Daily and Weekly. Weekly will always call
> Daily, but Daily can run on its own. I currently use a global temp
> table because certain things I do with it won't work with a local temp
> table or table variable.
>
> I have been trying to get code so that if the table already exists, it
> just keeps going and uses it, but creates the temp table if it doesn't
> exist. Unfortunately, no matter how I try to do it, it always attempts
> to create it, raising an error and breaking the code.

If first this had to do with recompiles, but it appears that if
you include CREATE TABLE statements for the same table twice in a
batch, the compilation fails. That's why it works when you put
the later CREATE TABLE statement in EXEC().

But I agree with David, a permanent table is probably better. Global
temp tables is a funny invention, and I very rarely find any use for them.

Not that I know what you are trying to do, but I have an article on my
web site that may give you some tips:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]