query using case - 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 2005 Forums

Transact-SQL (2005)

query using case

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2010-03-09 : 05:26:45

Hi
I want to write a query like this:
if @parameter1=1 then : select * from table1 where id=@parameter2
and
if @parameter1=2 then : select * from table1 where name=@parameter2
and
if @parameter1=3 then : select * from table1 where family=@parameter2

How can i write this query using 'case' ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-09 : 05:33:03

select * from table1
where
(
id=@parameter2
and
@parameter1=1
)
or
(
name=@parameter2
and
@parameter1=2
)
or
(
family=@parameter2
and
@parameter1=3
)

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-09 : 05:39:05

could you please try this

select * from table1 where
case when @parameter1=1 then id
when @parameter1=2 then name
when @parameter1=3 then family else null end=@parameter2

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-09 : 05:44:43

quote:
Originally posted by madhivanan

select * from table1
where
(
id=@parameter2
and
@parameter1=1
)
or
(
name=@parameter2
and
@parameter1=2
)
or
(
family=@parameter2
and
@parameter1=3
)

Madhivanan

Failing to plan is Planning to fail

Hi Mr.Madhi..This sounds good..
could you please tell me ,wat about my query interms of performance oriented(Ref:please see my previous reply)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-09 : 05:47:24

CASE expression in the WHERE clause may slow down the performance.
But OP should test the solutions with large set of data

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-09 : 05:52:30

quote:
Originally posted by madhivanan

CASE expression in the WHERE clause may slow down the performance.
But OP should test the solutions with large set of data

Madhivanan

Failing to plan is Planning to fail

Thank you Mr.Madhi for your response.much Appreciated.Iam learnig from you alot here...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources