CASE WHEN within the Where clause.  
Author Message
JeremyBentvelzen





PostPosted: Wed Nov 07 02:14:42 PST 2007 Top

SQL Server Developer >> CASE WHEN within the Where clause.

Hi all.
Any syntax examples on using a "CASE WHEN' within the where clause. Is it
legal? Need to use different searchcriteria based on a table value. Something
like...



AND (CASE WHEN OrderHead.FieldX = 1 THEN OrderLine.FieldN = 4 ELSE
OrderLine.FieldN = 2)......


Somehow I have ended in a situation that needs something like this :-)

--
Thanks all
Regards Geir

SQL Server72  
 
 
Tibor





PostPosted: Wed Nov 07 02:14:42 PST 2007 Top

SQL Server Developer >> CASE WHEN within the Where clause. The CASE expression returns a value. So your WHERE clause should look something like:



AND OrderLine.FieldN =
(CASE WHEN OrderHead.FieldX = 1 THEN 4 ELSE 2 END)......

ButI strongly recommend reading below great article:
http://www.sommarskog.se/dyn-search.html

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi




> Hi all.
> Any syntax examples on using a "CASE WHEN' within the where clause. Is it
> legal? Need to use different searchcriteria based on a table value. Something
> like...
>
>

> AND (CASE WHEN OrderHead.FieldX = 1 THEN OrderLine.FieldN = 4 ELSE
> OrderLine.FieldN = 2)......
>
>
> Somehow I have ended in a situation that needs something like this :-)
>
> --
> Thanks all
> Regards Geir