CASE WHEN within the Where clause. - gei

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...


WHERE OrderHead = @OrdrerHead AND ProduktNr LIKE @ProduktNr
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

CASE WHEN within the Where clause. - Tibor Karaszi

The CASE expression returns a value. So your WHERE clause should look something like:

WHERE OrderHead = @OrdrerHead
AND ProduktNr LIKE @ProduktNr
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