Conditional where clause in SQL

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 where clause in SQL

Author

Topic

nssjari
Starting Member

46 Posts

Posted - 2005-07-07 : 04:03:20

Is there anything like Conditional WHERE clause ...

If select field value true then apply where clause else
if select field value false do not apply the where clause ...

Is there anything like this ... please forward ur comments ...

Thanks in advance
Jari

Jari
Computer Engg

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-07-07 : 04:22:01

use case in your where statement...

where field=case when condition1 is true then criteria1 else criteria2 end

--------------------
keeping it simple...

nssjari
Starting Member

46 Posts

Posted - 2005-07-07 : 04:33:16

Does it work in SQL the way you said ...?

quote:
Originally posted by jen

use case in your where statement...

where field=case when condition1 is true then criteria1 else criteria2 end

--------------------
keeping it simple...

Jari
Computer Engg

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-07 : 04:40:29

You need something like this

If exists(select...)
--query with where clause
else
--query without where clause

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-07-07 : 04:43:50

don't take my word for it, try it

--edit

that was bad of you jari, double posting like that...

they've already hinted at a case and yet you did not even try it??
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52008

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-07 : 07:56:12

Possible but not recommended:

select ...
from ...
where (field = 1 and <if condition>)
   or (field = 0 and <else condition>)

better:

select ...
from ...
where field = 1 and <if condition>
union
select ...
from ...
where field = 0 and <else condition>)

I guess you want to create kind of generic sp. If this is the case better create two procs instead.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources