>Kevin, I asked whether the MSSQL index could identify nulls and/or whether the optimizer is smart enough to winkle it out from inside the coalesce. If the answer to either is "no", then a row scan is needed against every row potentially in the resultset- which could be all million rows. I believe you're saying that the answer is "no" for both cases. ;-)
>
>If you would only consider SPT, you could craft a "flexible" "efficient" parameterized select that doesn't have to handle NULL parameters; it simply leaves them out.
>
>Example:
>
>

SELECT * FROM Customers
> WHERE State LIKE @State

>
>As long as State is indexed, the above is easily optimized, as well as being cached for subsequent similar queries. It's not as clever or interesting as a SP that can do almost as much (your SP has no way to search for NULL field values, right?) but it obeys the KISS principle. There are few vendor-specific goodies that can deliver a more efficient or flexible outcome than the above!
>
>Your new select is more efficient than the coalesce, but still comparatively bloated for the majority of queries that don't need every field tested. You might consider a one-off NULL parameter trap at the start and then rely on the optimizer to ignore plain % comparisons. But it's *still* using more cycles than a basic select that does exactly what the user needs.
>
>Just to be clear: I'm not arguing against SP. Of course SP have value, which is why we've used them in Oracle and SQL Server since 1995. But sometimes it makes better sense to craft specific queries, presumably using SPT. Why not keep both in your holsters? ;-)

I believe it's because so many are convinced that SP-only is the only way to go and that dynamic sql is slow, dangerous or some other misconception.

I'm not arguing against SP either. Like you I want both guns and end up debating with people because of their misconceptions. :)