CASE WHEN Equivalent in WHERE Clause?

[laurenquantrell]

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

[Plamen Ratchev]

You have to change your CASE so it properly returns expression:

SELECT c.FirstName,
c.LastName
FROM Contacts AS c
WHERE
CASE WHEN @FirstName = 'Bob' THEN
CASE WHEN c.BlueHair = 1 THEN 'Y' END
ELSE
CASE WHEN @FirstName = 'Frank' THEN
CASE WHEN c.PastaEater = 1 THEN 'Y' END
ELSE
CASE WHEN c.HatSize 5 THEN 'Y' END
END
END = 'Y'

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[--CELKO--]

>Is there an equivalent construction to the CASE WHEN statement that can be used in the WHERE clause? <<

1) There is no CASE statement in SQL; but we do have a CASE
expression. You still have a procedural mindset and have not started
thinking in sets.

2) I am guessing that you want to write a **predicate** something like
this:

DECLARE @FirstName NVARCHAR(35);

SELECT first_name, last_name
FROM Contacts
WHERE
CASE WHEN @first_name = 'Bob' AND bluehair = 1
THEN 'T'
WHEN @first_name = 'Frank' AND pastaeater = 1
THEN 'T'
WHEN hat_size 5.0
THEN 'T' ELSE 'F' END = 'T';

[Hugo Kornelis]

On Fri, 28 Mar 2008 11:40:05 -0700 (PDT), --CELKO-- wrote:

>>Is there an equivalent construction to the CASE WHEN statement that can be used in the WHERE clause? <<

1) There is no CASE statement in SQL; but we do have a CASE
expression. You still have a procedural mindset and have not started
thinking in sets.

2) I am guessing that you want to write a **predicate** something like
this:

DECLARE @FirstName NVARCHAR(35);

SELECT first_name, last_name
FROM Contacts
WHERE
CASE WHEN @first_name = 'Bob' AND bluehair = 1
THEN 'T'
WHEN @first_name = 'Frank' AND pastaeater = 1
THEN 'T'
WHEN hat_size 5.0
THEN 'T' ELSE 'F' END = 'T';

Hi Joe,

This is not equivalent to what Lauren Quantrell wanted. The version
posted by Plamen Ratchev is better.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Ed Murphy]

la*************@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.