if ... else statement question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

if ... else statement question

Author

Topic

cr488
Starting Member

23 Posts

Posted - 2006-04-04 : 02:51:09

Here is a simple example:

if {option} = 1
begin
select state
into #temp
from state
where state in ('CA', 'GA')
end

else
begin
select state
into #temp
from state
where state not in ('CA', 'GA')
end

I got a error message saying I already have #temp exsited.

I thought if ..else statement should be able to take care of this. If option = 1 then #temp with 'CA' and 'GA' in it. If option =2 or 3 then #temp with all other states. I want the #temp table to have the same name. Is something wrong with my query here?

Thanks in advance for your help!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-04 : 02:59:16

you can't use the same temp table name in SELECT .. INTO.
2 Option
1. Change of the #temp to #temp2
2. pre-create the #temp table and use INSERT INTO #temp (...) SELECT ...

KH
Choice is an illusion, created between those with power, and those without.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-04 : 03:00:03

nothing wrong with your code...its giving error that #temp table is already exististing..

so once you finish processing with your temp table just drop the table

Drop table #temp

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-04 : 03:01:57

It is parse error. Use the suggested method

Madhivanan

Failing to plan is Planning to fail

cr488
Starting Member

23 Posts

Posted - 2006-04-04 : 03:11:50

Thanks for you replies. I do want to have the same temp table name since I am going to use it later on in a subquery.

So that my only choice right now is :

declare table @temp (state char(2))

if {option} = 1
begin
insert into @temp (state char(2)
select state
from state
where state in ('CA', 'GA')
end

else
begin
insert into @temp (state char(2)
select state
from state
where state not in ('CA', 'GA')
end

is it correct? I am new to the sql server, I really appreciate for your help!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-04 : 03:16:20

That seems good

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-04 : 04:57:19

Note that for this simple problem, you can use this...

declare @state table (state char(2))
insert into @state
          select 'CA'
union all select 'GA'
union all select 'NY'
union all select 'IL'

declare @option int

set @option = 2

select state
from @state
where (@option = 1 and state in ('CA', 'GA'))
   or (@option = 2 and state not in ('CA', 'GA'))

I don't know if this will help for your actual problem though

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

cr488
Starting Member

23 Posts

Posted - 2006-04-06 : 23:22:07

Thanks for your help. Actually I do need to select the data from table and insert into a temp table, it has more than 10000 records....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources