Posted by Erland Sommarskog on 10/01/40 11:25

joshsackett (joshsackett@gmail.com) writes:
> Morgan:
> I don't understand quite what you mean. Can you use my query above and
> fit it to your example?

What he said is that:

WHERE (CASE WHEN @COUNTY IS NOT NULL COUNTY = @COUNTY ELSE COUNTY LIKE
'%' END)

does not work, because you have mixed the CASE expression with the WHERE
condition. You could say:

WHERE COUNTY = CASE WHEN @COUNTy IS NOT NULL THEN @COUNTY ELSE COUNTY END

or briefer:

WHERE COUNTY = coalesce(@COUNTY, COUNTY)

(coalesce is just syntactic sugar for CASE WHEN x IS NOT NULL THEN x WHEN
....)

Or, as said earlier in the thread you could go for some elaborate trick for
dynamic searches.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

• Next in forum: Re: All records within x minutes of each other
• Prev in forum: Re: Rewrite a WHERE clause
• Thread view: Re: Rewrite a WHERE clause

[Reply to this message]