Conditional statement with temp table creation error

Question

I've been racking my brain but can't see what the problem is with the query below.
I've created a temporary table which can hold one of two values.
The idea is that if 1 is stored it executes the first part of the code below (This) and if it's 2 it executes the code further below (That).
Trouble is the code won't run because it "seems" to think that both parts of the code will be executed ... I think. Any ideas?

create table #Globals(G1 smallint);
insert into #Globals values (1); 
--insert into #Globals values (2); 

if (select G1 from #Globals) = 1
  select 'This' as field1 into #x;
else if (select G1 from #Globals) = 2
  select 'That' as field1 into #x;

Msg 2714, Level 16, State 1, Line 8
There is already an object named '#x' in the database.

Even if you try to drop the temp table before the second insert you will get the same exception. Try using my suggestion instead. — Zohar Peled, May 18 '15 at 11:07

score 0 · Answer 1 · answered May 18 '15 at 10:22

0

You should use EXISTS for the logical flow of the code:

if exists (select G1 from #Globals where G1 = 1)
  begin
    select 'This' as field1 into #x;
  end
else if exists (select G1 from #Globals where G1 = 2)
  begin
    select 'That' as field1 into #x;
  end

answered May 18 '15 at 10:22

John Bell

2,350
1
14
23

This doesn't solve OP's problem – ughai May 18 '15 at 10:27
Then I would assume that the temp table has already been created in that session. I always ensure that a drop table of the temp table is the last line of my code, to make sure when I'm testing/running adhoc operations, I don't get this error. There's no way that the temp table can be created twice unless both conditions equate to true, in which case the problem lies in your logic, and not the flow. – John Bell May 18 '15 at 10:37
I have run the above code with different temp table name (#x987) in a new session and I still get the error (Msg 2714, Level 16, State 1, Line 11 There is already an object named '#x987' in the database.) – Fred_Scuttle May 18 '15 at 10:48

Zohar Peled · Answer 2 · 2015-05-18T10:34:07.673

Instead of using select into, simply create #x before the condition use insert into:

create table #x (field1 char(4));

if (select G1 from #Globals) = 1
    insert into #x(field1) values('This');
else if (select G1 from #Globals) = 2
    insert into #x(field1) values('That');

Another option is to use CASE:

create table #x (field1 varchar(7));
declare @g1 int
select @g1 = g1 from #globals

insert into #x(field1) values(
    case when @g1 = 1 then 'this'
         when @g1 = 2 then 'that'
    else
         'default'
    end
);

The case option will allow you to use multiple different options (you can specify how many when clauses as you wish), and also I've demonstrated a way to query your temporary table only once instead of each of the if...else if... else if... branches. (of course, you can do the same with the if...else)

Conditional statement with temp table creation error

2 Answers2