Posted by M Bourgon on 08/30/06 15:16

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.

[Back to original message]