Re: CASE WHEN Equivalent in WHERE Clause?
- From: Ed Murphy <emurphy42@xxxxxxxxxxxx>
- Date: Sun, 30 Mar 2008 21:05:00 -0700
laurenquantrell@xxxxxxxxx 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.
.
- References:
- CASE WHEN Equivalent in WHERE Clause?
- From: laurenquantrell
- CASE WHEN Equivalent in WHERE Clause?
- Prev by Date: Re: min_active_rowversion ( was Re: Triggers and Flag bit)
- Next by Date: Internal query processor error
- Previous by thread: Re: CASE WHEN Equivalent in WHERE Clause?
- Next by thread: Sequential Time and Cumulative Counts
- Index(es):
Relevant Pages
|
