How do you create ##Temp tables if they don't exist, use them if they do?

Giganews Newsgroups
Subject: How do you create ##Temp tables if they don't exist, use them if they do?
Posted by:  M Bourgon (bourg…@gmail.com)
Date: 30 Aug 2006

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.

create table ##load_file_log (id int identity(1,1),contents
varchar(1000))
insert into ##load_file_log (contents) values ('test record')

IF object_id('tempdb..##load_file_log') IS not NULL
    print 'exists'
    ELSE
      create table ##load_file_log (id int identity(1,1),contents
varchar(1000))

select * from ##load_file_log
drop table ##load_file_log

If I change it to IS NULL, the same error occurs (Server: Msg 2714,
Level 16, State 1, Line 7
There is already an object named '##load_file_log' in the database.)

I have found one way to do it, but it seems a bit...clunky.

IF object_id('tempdb..##load_file_log') IS NULL
      exec ('create table ##load_file_log (id int identity(1,1),contents
varchar(1000))')

I'll use that for now, but is there something I missed?
Thanks.

Replies