By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,377 Members | 1,334 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,377 IT Pros & Developers. It's quick & easy.

CASE WHEN Equivalent in WHERE Clause?

P: n/a
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

Mar 28 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Mar 28 '08 #2

P: n/a
>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';

Mar 28 '08 #3

P: n/a
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
Mar 30 '08 #4

P: n/a
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.
Mar 31 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.