I hate dynamic querys as they are not optimized and a query plan cannot be cached….
Sql = some dyanamic sql string built via if logic…
Exec(sql) – will not cache an query execution plan.
In MS Sql sp_executesql caches query plans – very cool…
Really though, try to avoid them if possible….
Great read on the details…
Newest version of document: http://www.sommarskog.se/dyn-search-2008.html
http://www.sommarskog.se/dyn-search-2005.html – Version for SQL 2005 and Earlier