>Walter,
>
>Kevin's example *IS* using parameters, which is the reason for bending it into the way he did.
>
>Yeah, I guess I've misconstrued the effect of the Coalesce... I had assumed that with this:
>
>

WHERE LastName LIKE  '%'  +  COALESCE(@LastName,LastName)+  '%'  AND
>            FirstName LIKE '%' + COALESCE(@FirstName,FirstName) + '%' AND
>            Address LIKE '%' + COALESCE(@Address,Address) + '%' AND
>            City LIKE '%' + COALESCE(@City,City) + '%' AND
>            State LIKE '%' + COALESCE(@State,State) + '%' AND
>            Zip LIKE '%' + COALESCE(@Zip,Zip) + '%' ),

>
>If I entered 1% or 0=0 or Zip LIKE % in the Zip textbox on the displayed form, I could alter the intended behavior of the SQL. IOW I thought the coalesces were run just once, concatenating parameters' values into the SQL rather than being run on every row. I guess I need to slow down and check more carefully!

BTW, this construct is not creating an optimzal execution plan. A COALESCE will result in an index scan at most and will not perform the INDEX SEEK. When having multiple of suchs COALESCE constructs in one WHERE clause, is asking for performance problems on large tables. A carefully contructed dynamic SQL statement (with parameters) is outperforming such stored procedures, while the common wisdom is saying that stored procedures are faster.

If you're not constructing a dynamic SQL statement (You can also do this within a stored procedure and call sp_executesql manually) without the unneccasary baggage, you'll end up with a statment that is inefficient in a number of aspects.

Things of course will get worse when joins are involved. IF for example city is in a lookup table, you'll have to do a join and a COALESCE on that lookup table will have a dramatic effect, even if you're not doing a search on city at all.