Conditional processing problem...

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

SQL Server Development (2000)

Conditional processing problem...

Author

Topic

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2003-09-04 : 07:07:09

Can someone tell me if this is possible in a SP? If it is then how using SQL 2k can I do it?

select field1, field2, field3, field4 etc into #Tmp1 from tbl1

select case
when @var1 = 'A' then 1
INSERT INTO tblc (field1, field2)
SELECT field1, field2
FROM #Tmp1
when @va1 = 'B' then 2
INSERT INTO tblb (field1, field2, field3, field4)
SELECT field1, field2
FROM #Tmp1
else
INSERT INTO tbla (field1, field2, etc)
SELECT field1, field2, etc
FROM #Tmp1
end

Depending on @var1, I want to insert the values from a temp table i've created from some very complex queries...

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-04 : 07:38:04

Perhaps you can do all of this in one go:

INSERT INTO tblC (field1, field2, field3, field4, field5)
SELECT field1, field2,
CASE WHEN @var1 IN ('A', 'B', 'C') THEN field3 ELSE NULL END,
CASE WHEN @var1 IN ('B', 'C') THEN field4 ELSE NULL END,
CASE WHEN @var1 = 'C' THEN field5 ELSE NULL END,
FROM #Tmp1

What do you think?

Owais

Make it idiot proof and someone will make a better idiot

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2003-09-04 : 07:41:41

It would work if I was inserting into the same table.. Unfortunately i'm not... :(

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-04 : 07:50:08

aaah, I see now, said the blind man...

You can't use the SELECT statement like this, you need to use IF

IF (@var = 'A')
BEGIN
INSERT INTO tbla (field1, field2)
SELECT field1, field2 FROM #tmp
END

IF (@var = 'B')
BEGIN
INSERT INTO tblb (field1, field2)
SELECT field1, field2 FROM #tmp
END

IF (@var = 'C')
BEGIN
INSERT INTO tblc (field1, field2)
SELECT field1, field2 FROM #tmp
END

IF (@var NOT IN ('A','B','C'))
BEGIN
PRINT 'Bad Code!!'
END

Owais

Make it idiot proof and someone will make a better idiot

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2003-09-04 : 08:00:47

That worked...

Cheers

Did think about IF, but was thinking about IF...ELSE which doesn't like the same conditions...

Thanks again... :D

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-09-04 : 08:05:02

Hi, oops

messed up

Voted best SQL forum nickname...."Tutorial-D"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources