Posted by Steve Kass on 08/29/05 20:06

Josh,

You might try this, if your data is all typical alphabetic
values in English (no letters of the alphabet that come
after Z). I'm also assuming your collation is case-insensitive.

where COUNTY >= coalesce(@COUNTY,'A')
and COUNTY <= coalesce(@COUNTY,'ZZZZZZZZZZZZZZZZZZZ')
-- as many Z's as the declared length of the COUNTY column

This may be more efficient than your LIKE solution, but the
only way to be certain is to do some comparisons. You also
run the risk in situations like this of getting bad cached
query plans, at least if your actual query selects more than
the ID column, since the best query plan for @COUNTY = NULL
is a table scan and the best query plan for @COUNTY <> NULL
is a non-clustered index seek followed by a bookmark lookup.
If the second plan is cached and used later when @COUNTY is
NULL, it will be very inefficient. A way around this, should
it occur, may be to add something to force query recompilation.
In a stored procedure, that might be adding WITH RECOMPILE,
or in an sp or otherwise, adding something to the query that
will prevent autoparameterization (adding AND 1 = 1 to the
WHERE clause will do this, I believe)

Maybe that's more than you needed to know, but your question
suggests you are thinking about some important considerations
in query design.

Steve Kass
Drew University

joshsackett wrote:
> I have a WHERE clause that could be an "=" or a "LIKE" depending upon
> if the passed variable is populated or not. I would like to know the
> best way to write the WHERE clause to make it dynamically switch
> between the 2 and make best use of the indexes.
>
> CREATE TABLE myTable(ID INT PRIMARY KEY CLUSTERED, COUNTY VARCHAR(50))
> CREATE INDEX IDX_myTable_County ON myTable(COUNTY)
>
> DECLARE @COUNTY VARCHAR(50)
> SET @COUNTY = 'SANTA CLARA' -- Could also be SET @COUNTY = NULL
>
> SELECT ID FROM myTable
> WHERE COUNTY LIKE (CASE WHEN @COUNTY IS NOT NULL THEN @COUNTY ELSE '%'
> END)
>
> This does not seem like best practice to me because I am forced to use
> "LIKE" even when @COUNTY is populated with data. Ultimately I'd like:
>
> WHERE (CASE WHEN @COUNTY IS NOT NULL COUNTY = @COUNTY ELSE COUNTY LIKE
> '%' END)
>
> but that is incorrect syntax on "=".
>
> Also, I do not want to use a dynamically built statement. Is there a
> way around this?
>
> Thanks,
> Josh
>

[Back to original message]