Parameterizing T-SQL queries are a well-known task such as database programming and best practice in some tasks. It allows query plan reuse and eliminates the need of recompilation for multiple invocations of the same query that simply has different parameter values. However, there are times when parameterized queries might perform poorly, because they use cached query plans that are optimized for some non-representative set of parameter values. Forcing query execution plans, to a limited extent,...(
read more)
Read the complete post at http://sqlserver-qa.net/blogs/perftune/archive/2007/10/23/2212.aspx