RyanRandall (9/28/2007)
I'm with Adam, check out MVP Erland Sommarskog's article on this: http://www.sommarskog.se/dyn-search.htmlHowever, for the example presented by the article, can someone tell me what's wrong with simply this?...
selectcustomerid, companyname, country
from
customers
where
(companyname LIKE @companyname) AND (country LIKE @country)
This is able to deal with the different operators (NOT LIKE 'A%' can be dealt with by using '[0-9, b-z]%'), and can use indexes if they're there (i.e. avoid a table scan).
That was my first reaction, too. On re-reading the article I see that the point was not simply to write SQL which fits the given example. It was meant to be a generalizable solution for user-defined searches.
My habitual solution when building search forms has simply been to write code that conditionally concatenates SQL fragments which reflect user-selected criteria and operators. So the whole discussion here is interesting. Thanks.