conditional insert stmt - SQL Server Forums

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)

conditional insert stmt

Author

Topic

medotnet
Starting Member

14 Posts

Posted - 2007-08-20 : 07:50:03

Hi all,
I want to preserve values returned from select stmt to use them later in a conditional insert stmt as follows:
Select a1,a2,a3,a4 from A# where a1=100
save the returned values in parameters:

r3 = a3
r4 = a4

use condition to insert into B# as:

if r4>0
insert into B# values (r4,'Good')
else if r4<0
insert into B# values (r4,'bad')

thanks a lot for your help,
Adi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-20 : 08:20:50

See if this gives what you want

Select a1,a2,a3,a4
case when a3>0 then 'Good' when a3<0 then 'Bad' end as a3_status,
case when a4>0 then 'Good' when a4<0 then 'Bad' end as a4_status
from table

Madhivanan

Failing to plan is Planning to fail

medotnet
Starting Member

14 Posts

Posted - 2007-08-20 : 09:25:03

well thanks for your reply,
but still missing some parts where I want to preserve the values returned from previous select statement for the reason that I want to build a full insert statement.
plus can I use select with case as:
update B#
set status =
case when(sum(a2)-sum(a3))group by a1>0 then 'good'
case when(sum(a2)-sum(a3))group by a1<0 then 'bad'

thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 09:42:19

What? That is not valid SQL syntax.

Please post full query and your objective.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-20 : 09:44:07

Select status =
case when(sum(a2)-sum(a3))>0 then 'good' end
case when(sum(a2)-sum(a3))<0 then 'bad' end
from B#
group by a1

Madhivanan

Failing to plan is Planning to fail

medotnet
Starting Member

14 Posts

Posted - 2007-08-20 : 09:53:17

Thanks Madhivanan,
this was helpful

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources