Re: CASE WHEN Equivalent in WHERE Clause?

laurenquantrell@gmail.com wrote:

> Is there an equivalant construction to the CASE WHEN statement that
> can be used in the WHERE clause?
>
> For example, this works:
>
> SELECT
> FirstName = CASE WHEN c.FirstName = 'Bob' THEN
> 'Robert'
> ELSE
> c.FirstName
> END,
> c.LastName
> FROM
> Contacts c
> WHERE
> (c.FirstName = 'Bob')
> OR
> (c.FirstName = 'Robert')
>
> But is there a way to build somehting resembling this (without using
> Exec-SQL)
>
> @FirstName nvarchar(35)
>
> SELECT
> c.FirstName
> c.LastName
> FROM
> Contacts c
> WHERE
> CASE WHEN @FirstName = 'Bob' THEN
> c.BlueHair = 1
> ELSE CASE WHEN @FirstName = 'Frank' THEN
> c.PastaEater = 1
> ELSE
> c.HatSize > 5
> END END

If you have this few @FirstName values with special rules, then you
could also rewrite it like this:

WHERE (@FirstName = 'Bob' AND c.BlueHair = 1)
OR (@FirstName = 'Frank' AND c.PastaEater = 1)
OR (NOT (@FirstName IN ('Bob', 'Frank')) AND c.HatSize > 5)

Note that the third line is not written as
OR (@FirstName NOT IN ('Bob', 'Frank') AND c.HatSize > 5)
because @FirstName null would fail to satisfy it.