CASE in where clause?

I want to create SQL (maybe stored proc) using a SELECT statement that can
add WHERE conditions if (and only if) values are sent to it. For example:
SELECT ID, Person
FROM People
WHERE LastName LIKE '%@.var1%' +
CASE WHEN @.var2 IS NOT NULL THEN ' AND TypeCode = ' + @.var2
ELSE ''
END
...etc
Is this possible? Thanks
David> SELECT ID, Person
> FROM People
> WHERE LastName LIKE '%@.var1%' +
> CASE WHEN @.var2 IS NOT NULL THEN ' AND TypeCode = ' + @.var2
> ELSE ''
> END
Did you mean:
SELECT ID, Person
FROM People
WHERE LastName LIKE '%' + @.var1 + '%'
AND TypeCode = COALESCE(@.TypeCode, TypeCode)
or more verbosely
SELECT ID, Person
FROM People
WHERE LastName LIKE '%' + @.var1 + '%'
AND TypeCode = CASE WHEN @.TypeCode IS NULL THEN TypeCode ELSE @.TypeCode END
CASE is an expression that returns a single value. It is *NOT* used for
flow control.|||could I suggest a slight amendment (and add a question):
SELECT ID, Person
FROM People
WHERE LastName LIKE '%' + @.var1 + '%'
AND (@.TypeCode is null OR TypeCode = @.TypeCode)
this way when @.typecode is null the lazy evaluation of SQL will mean it
won't even look at the second statement.
I've found if it's put the other way:
WHERE Typecode = @.TypeCode OR @.TypeCode is null
it will first evaluation Typecode = @.TypeCode, causing it to scan that
field, then it will evaluate the second statement.
I'm not sure but I suspect your suggestion of Typecode =
COALLESCE(@.TypeCode, TypeCode) will cause it to read the typecode field
even when a null value is passed to it.
Cheers
Will|||> I've found if it's put the other way:
> WHERE Typecode = @.TypeCode OR @.TypeCode is null
> it will first evaluation Typecode = @.TypeCode, causing it to scan that
> field, then it will evaluate the second statement.
There is no guarantee in the order of execution within a clause, it could go
left to right, it could go right to left.

> I'm not sure but I suspect your suggestion of Typecode =
> COALLESCE(@.TypeCode, TypeCode) will cause it to read the typecode field
> even when a null value is passed to it.
Really hard to say without table structure, sample data, row counts, and the
ability to actually test and examine query plans.|||"There is no guarantee in the order of execution within a clause, it
could go
left to right, it could go right to left. "
I know you're an MVP, and do indeed know SQL server way better than me,
but still I feel I have to ask - are you sure about this?
it's just that I've found on several occasions that if I switch the 2
statements around it changes the execution plan fundamentally, and
doesn't do any table scans (when the value is null of course). If the
order was not an issue then surely switching the order would not affect
the query?
Thanks
Will|||> it's just that I've found on several occasions that if I switch the 2
> statements around it changes the execution plan fundamentally, and
> doesn't do any table scans (when the value is null of course). If the
> order was not an issue then surely switching the order would not affect
> the query?
I didn't say order CAN'T be an issue. Just don't rely on this kind of
short-circuiting to work consistently.|||Fundamental mistake! There is no CASE **statement** in SQL. There is
a CASE **expression**; remember programming 101? Expressions return
scalar values, not control of execution flow.
SELECT person_id, person_name
FROM People
WHERE last_name LIKE '%' + @.var1 + '%'
AND foobar_code = COALESCE(@.my_code, foobar_code) ;
Even for an example, you had some pretty awful data element names.
Something can be a type of something or a code. It cannot be both.
There is no such thing as just an "id" -- it has to identify something
in particular.
You might want to get a book on SQL and data modeling.|||Will (william_pegg@.yahoo.co.uk) writes:
> could I suggest a slight amendment (and add a question):
> SELECT ID, Person
> FROM People
> WHERE LastName LIKE '%' + @.var1 + '%'
> AND (@.TypeCode is null OR TypeCode = @.TypeCode)
> this way when @.typecode is null the lazy evaluation of SQL will mean it
> won't even look at the second statement.
It's unclear here what you mean with "second statement".
@.TypeCode IS NULL OR TypeCode = @.TypeCode
would be the same as
TypeCode = @.TypeCode OR @.TypeCode IS NULL
when it comes to performance.
If you've seen something else, it might have been a mirage due to
caching.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||My Apologies,
I really should stop questioning MVPs, you guys don't ever seem to be
wrong. I tried to recreate the situation and couldn't. My reasoning was
based on several queries I've debugged in the past that were displaying
a strange execution plan, and then by switching the statements around
(the "or" statements), increased performance dramatically (from 4s to
50ms). I assumed at the time that it was just a simple grammar case
that you could apply lazy evaluation, and that was how SQL was
optimising it, however it appears that there's more stuff going on.
perhaps the optimiser was having an odd day, or more likely there were
incorrect index statistics monkeying things up, but anyway, thanks for
clearing it up.
Cheers
Will|||The problem is that SQL is set-oriented and not sequential. The THEN
clauses in a CASE expression (which includes COALESCE()) all have to be
evaluated to determine the data type of the whole expression. It does
not matter if some of them are unreachable.
COALESCE correctly promotes its arguments to the highest data type in
the expression:
13 / COALESCE(CAST(NULL AS INTEGER), 2.00) = 6.5
The proprietary ISNULL() uses the first data type and gets things wrong
13 / ISNULL(CAST(NULL AS INTEGER), 2.00) = 6
You would need to write:
13 / ISNULL(CAST(NULL AS DECIMAL(4,2)), 2.00)sql