Need a CASE WHEN in the IN statement

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)

Need a CASE WHEN in the IN statement

Author

Topic

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-02-14 : 23:11:50

Hi all,

declare @NoOfPeople int
select @NoOfPeople=2
select SID from Weekyl_price where SID IN (
CASE @NoOfPeople
WHEN 1 THEN (1,2)
WHEN 2 THEN (4,5,6)
END)
)

Can I use Case inside an IN statement? I tried thatbut it seems that it cant work
So I tried this

declare @NoOfPeople int, @SIDs1 nvarchar(40), @SIDs2 nvarchar(40)
select @NoOfPeople=1, @SIDs1='1,2,3,4', @SIDs2='5,6,7,8,334'

select SID from Weekly_price where SID IN (
CASE @NoOfPeople
WHEN 1 THEN (select * from inline_split_me(@SIDs1))
WHEN 2 THEN (select * from inline_split_me(@SIDs2))
END)

inline_split_me is a function that turns '1,2,3,4' into a table
reference:http://www.sommarskog.se/arrays-in-sql.html#tblnum

Please advise

**Jonathan**

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-02-14 : 23:17:08

The second method cant work too

**Jonathan**

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-14 : 23:18:13

It will be best if you avoid CASE and use IF:

If @NoOfPeople = 1
	select SID from Weekyl_price where SID IN (1,2)
else if @NoOfPeople = 2
	select SID from Weekyl_price where SID IN (4,5,6)

It looks more readable and is more efficient than using CASE.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 23:23:42

[code]
declare @NoOfPeople int,
@SID nvarchar(40),
@SIDs1 nvarchar(40),
@SIDs2 nvarchar(40)

select @NoOfPeople = 1,
@SIDs1 = '1,2,3,4',
@SIDs2 = '5,6,7,8,334'

select @SID = case @NoOfPeople when 1 then @SIDs1 when 2 then @SIDs2 end

select *
from inline_split_me(@SID)

select SID
from Weekly_price a inner join inline_split_me(@SID) b
on a.SID = b.Value
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 23:25:48


declare @NoOfPeople int
select 	@NoOfPeople = 2

select 	SID 
from 	Weekyl_price 
where 	(@NoOfPeople = 1 and SID IN (1,2))
or	(@NoOfPeople = 2 and SID IN (4,5,6))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources