mohd.nizamuddin (5/20/2010)
Very well written, a nice article.Can you please put some light on the excution plan of this SP, while writing dynamic SQL.
As per knowledged, the execution plan won't be resued.
Please suggest.
Execution plans will be cached when using dynamic SQL, and SQL Server will cache a separate plan for each combination of the values in the parameters for sp_executesql.
To test that this actually works I suggest you run Profiler and add SP:CacheHit, SP:CacheMiss and SP:CacheInsert events when you use sp_executesql.