Erland Sommarskog, SQL Server MVP, and a much smarter SQL Server professional than me has updated his article on Dynamic Search Conditions available at http://www.sommarskog.se/dyn-search.html. A great document to begin with, it now to covers the method with inline table functions. Erland mentions that he found that working with the article, to use the function effectively, you need to interpolate you parameter values entirely. However, this increases the risk of SQL injection. But if you are on SQL 2000 and cannot give users SELECT permissions, this is still a very valueable method.
He also added a section "When Caching Is Not Really What You Want" to discuss the case when you may want to have different query execution plans depending on the input values.
Finally, he also added a demo to the main article on dynamic SQL, to wit the case of scripting from SQL Server Management Studio with forced parameterisation off and on.
Check out this great article,
-Kevi
