I second the sommarskog.se site (which gretchkosiy 2550 refers to above) - that is an excellent writeup which includes gotchas for SQL 2008 SP1 and dynamic SQL if I recall correctly.

Having had some even more insanely complex queries (many conditions) suffering because of the complete failure of the SQL query engine to short-circuit effectively, I can say that I have resorted to this approach after trying every other one (recompile, optimize for unknown, etc.) and I feel very confident saying that at least as of SQL 2008 SP1, dynamic parameterized SQL is THE way to go for proper index usage and performance.

It is a pain to debug, and it's awfully weird to have SQL generating SQL in a stored proc (in my opinion the stored proc compiler should have been smarter) just to build this kind of search query, but it just plain works. I've seen queries that performed 10x faster as dynamic parameterized SQL vs. static short-circuiting style code - all due to the ability for the right indexes to be used. I tried to contact Microsoft once to ask why the engine didn't recognize this use case, but I never heard back.

I'm certain not all complex search style where condition procs (which often require many OR conditions if statically coded and are therefore not easily SARG-able) need to be dynamic parameterized SQL (for example, if you're dealing with smaller tables - say 10,000 to 100,000 rows - I don't think it's a huge difference). For larger tables or complex sets/long lists of dynamic conditions, I think, as of today at least, it's a good way to go.

As always, test it for yourself, as every environment is different.

Thanks - good article!