CASE statement with more than one condition

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)

CASE statement with more than one condition

Author

Topic

jn-at-uk
Starting Member

20 Posts

Posted - 2005-04-12 : 12:23:20

I have a case statement where after checking a few conditions I want to create a temp table & insert values in my THEN section. Can this be done?

CASE
WHEN @Type = 'a' and @Result = 100 THEN create Table #Final(FDate datetime)
insert #Final(FDATE)
select left(abstimestamp,12) From #Temp group by left(abstimestamp,12) order by left(abstimestamp,12)

WHEN @Type = 'a' and @Result = 41 THEN create Table #Final(Result int)
insert #Final(Result)
select count(sessionid) From #Temp group by left(abstimestamp,12) order by left(abstimestamp,12)

WHEN @Type = 'b' and @Result = 100 THEN create Table #Final(FDate datetime,Visits int)
insert #Final(FDate,FDATE)
select count(sessionid), left(abstimestamp,12) From #Temp group by left(abstimestamp,12) order by left(abstimestamp,12)

WHEN @Type = 'b' and @Result = 41 THEN create Table #Final(FDate datetime,Visits int)
insert #Final(Visits,FDATE)
select count(sessionid), left(abstimestamp,12) From #Temp group by left(abstimestamp,12) order by left(abstimestamp,12)
END

Kristen
Test

22859 Posts

Posted - 2005-04-12 : 12:33:20

IF @Type = 'a' and @Result = 100
BEGIN
create Table #Final(FDate datetime)
insert #Final(FDATE)
select left(abstimestamp,12) From #Temp group by left(abstimestamp,12) order by left(abstimestamp,12)
END
ELSE
IF @Type = 'a' and @Result = 41
BEGIN
create Table #Final(Result int)
insert #Final(Result)
select count(sessionid) From #Temp group by left(abstimestamp,12) order by left(abstimestamp,12)
END
ELSE
IF @Type = 'b' and @Result = 100
BEGIN
create Table #Final(FDate datetime,Visits int)
insert #Final(FDate,FDATE)
select count(sessionid), left(abstimestamp,12) From #Temp group by left(abstimestamp,12) order by left(abstimestamp,12)
END
ELSE
IF @Type = 'b' and @Result = 41
BEGIN
create Table #Final(FDate datetime,Visits int)
insert #Final(Visits,FDATE)
select count(sessionid), left(abstimestamp,12) From #Temp group by left(abstimestamp,12) order by left(abstimestamp,12)
END

Kristen

jn-at-uk
Starting Member

20 Posts

Posted - 2005-04-12 : 17:02:16

hi kristen,

i am aware of the if, Begin & end statement. I just wanted to know if this could be done in a case statement to.

I don't think so by the looks of it. I will revert back to my if statements i guess.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-12 : 20:17:01

The the functionality is very similar, however IF/Else controls the flow of execution where as CASE is an expression that can't stand on its own. you can use CASE wherever sql syntax accepts an expression. You can't use IF in a sql statement. You can't use CASE outside a sql statement.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources