February 11, 2004 at 8:38 am
I want to create a temporary table with different specs based upon a condition in a variable. Is using a different table name the only way around this? This sample returns the error "There is already an object named '#process' in the database." even though only the first create should execute.
declare @fmt as varchar(1)
set @fmt = 'B'
if object_id('tempdb..#process') is not null drop table #process
if @fmt = 'B'
begin
create table #process
(RT int,
Bureau varchar(3),
YYYYMM varchar(20),
dim varchar(1),
msr varchar(1),
val decimal(11,2))
end
if @fmt = 'O'
begin
create table #process
(RT int,
Bureau varchar(3),
Office varchar(3),
YYYYMM varchar(20),
Dim varchar(1),
msr varchar(1),
val decimal(11,2))
end
February 11, 2004 at 8:55 am
I believe this is an issue in the compile portion of the query engine in that it recognizes the CREATE TABLE #process and has built in memory then sees again and cannot handle. I suggest reporting this to MS to see if they say feature or issue.
February 11, 2004 at 9:05 am
Antares686,
Thank you for your very quick response. I will report back if I hear from MS.
JFW
February 11, 2004 at 9:55 am
Just as an after thought. Why not create the table outside of the if statements with any fields that are always present and then use Alter Table ... Add to add the variable columns inside the if statements?
February 11, 2004 at 10:43 am
lenmcmanotony,
Great idea. Thanks, JFW
February 11, 2004 at 10:54 am
I agree a very good suggestion.
February 12, 2004 at 7:00 am
Unfortunately, ALTER TABLE runs into the same problem you experienced trying to declare the same table with two different schemas. Since the "ALTER TABLE DROP COLUMN" statement would follow the "CREATE TABLE" statement, the structure of the table as seen by SQL Server following the compilation would not include the column you want to conditionally drop, thus causing an execution error.
Why can't you create a table that includes all of the columns you need and just ignore the columns that are not needed based on @fmt.
Mike
February 12, 2004 at 7:20 am
I tried this and got not issues, also tried with creating the table as a whole and adding office at the end.
declare @fmt as varchar(1)
set @fmt = 'b'
if object_id('tempdb..#process') is not null drop table #process
create table #process
(RT int,
Bureau varchar(3))
if @fmt = 'O'
BEGIN
alter table #process add
Office varchar(3)
END
alter table #process add YYYYMM varchar(20)
alter table #process add dim varchar(1)
alter table #process add msr varchar(1)
alter table #process add val decimal(11,2)
select * from #process
go
also tried this way
declare @fmt as varchar(1)
set @fmt = 'b'
if object_id('tempdb..#process') is not null drop table #process
create table #process
(RT int,
Bureau varchar(3),
Office varchar(3),
YYYYMM varchar(20),
dim varchar(1),
msr varchar(1),
val decimal(11,2)
)
if @fmt = 'b'
BEGIN
alter table #process drop column Office
END
select * from #process
go
did not get an error any time.
February 12, 2004 at 12:28 pm
mkeast,
Thank you for your suggestion. I could probably make it work ignoring unused columns, but I ended up using code similar to Antares686 example above.
Thanks to everone for their ideas.
JFW
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply