I have to throw in here. I have had similar thoughts as to the author in the past. However, at the time, I was working on low volume systems.
For the last year and a half I have been working on a much higher volume (1,500 transactions a minute) system with a complex structure (900+ tables) and have seen the evils of using dynamic SQL for all the points given in this forum. Since I suspect the author is working on much lower volume systems, I am betting they do not see the ill effects (yet). From that perspective, let me say:
1)Performance: When SQL is consistently busy, recompiling plans on every execution drains resources in a way that is very noticeable to an end user. This is, of course, death to a large system.
2)Performance: Yes, I have seen some queries run faster under dynamic SQL, but I have seen the opposite more often. When a query is faster under dynamic SQL, it is usually because the search screens are written TOO flexibly, and the query cannot anticipate all reasonable combinations of parameters. I’d strongly suggest instilling in the development team that separate searches for different TYPES of searches be developed, or have your procs “sense” what type of search the user is trying to perform and route the query to an appropriate static stored proc so that the query plan can be cached.
3)Performance: When using complex dynamic SQL, it’s just too easy to get knocked off an index and start a table scan. Again, you won’t see this too often on smaller systems that join three 40,000 record tables, but when you’re working with 6 1,000,000+ record tables, you’ll see it immediately.
4)Maintainability: When there are 20 different coders in the environment, any pattern that is not well known (no matter how worthy) is confusing.
5)Security: Dynamic SQL will execute with different security contexts depending on how you connect (Trusted connection, SQL Login, etc) and you will find that if you start implementing security that the dynamic SQL queries may start not having access to tables!!! This one bit me very hard!
I say all this not to take a shot at anyone, but hopefully to share my learned experience.
Ben