RVO (3/13/2013)
My boss is a professional. He was a developer recently.I saw some of his stored procedures - good code.
I think he has a point. A few years ago at one large bank
production support team had big problems debugging complex long stored procedures
with lots of dynamic SQL. Production Support Team Lead just begged us to avoid using too much dynamic SQL.
Some people argue that for dynamic SQL, SQL Server engine cannot build optimized execution plan.
Not sure who's right who's wrong.
I'm sure. Your boss and Production Support Team are wrong on the following:
"for dynamic SQL, SQL Server engine cannot build optimized execution plan"
Now, you can write crap code in any form, dynamic or non-dynamic.
For the issue you are solving, properly written dynamic SQL will outperform any other solution. If you try, you will find it yourself! For dynamic-SQL optimizer will find and cach the best possible plans for each of SQL variation which will be built. If you use IF or CASE WHEN based solution, optimizer will not be able to come up with effective plan for any of the cases. Why? Gail Shaw explained it very well in her blog. If you have a doubt - just check it yourself.
Please remember, that there were many people around who argued that the Earth is flat, as if it would be round, we would fall down from it. http://en.wikipedia.org/wiki/Flat_Earth_Society
😉