I hate dynamic querys as they are not optimized and a query plan cannot be cached….

Example:

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

Older version

http://www.sommarskog.se/dyn-search-2005.html – Version for SQL 2005 and Earlier