CASE WHEN Equivalent in WHERE Clause?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

laurenquantrell@gmail.com - 28 Mar 2008 16:51 GMT

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

Reply to this Message

Plamen Ratchev - 28 Mar 2008 17:36 GMT

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

Reply to this Message

--CELKO-- - 28 Mar 2008 19:40 GMT

>> 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';

Reply to this Message

Hugo Kornelis - 30 Mar 2008 22:50 GMT

>>> Is there an equivalent construction to the CASE WHEN statement that can be used in the WHERE clause? <<
>
[quoted text clipped - 16 lines]
> 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.

Signature

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

Reply to this Message

Ed Murphy - 31 Mar 2008 05:05 GMT

> Is there an equivalant construction to the CASE WHEN statement that
> can be used in the WHERE clause?
[quoted text clipped - 33 lines]
> 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.

Reply to this Message