I am performing some knowledge discovery that what may be the bad effects of using dynamic SQL in SQL Server environment. So while going through some good articles on it i have figured out following major issues/side effects of dynamic SQL
- Query plans of Dynamic SQL are not cached so not reused
- Dynamic SQL can put you vulnerable for SQL injection attack
- Messy quotation marks and spacing complicate the query writing
- Network traffic is increased as compared to a USP executed
- Generating the dependencies through various methods becomes unreliable as objects used in dynamic SQL can not be traced by system views
- Ownership chaining is skipped hence permissions are compromised
These major reasons are enough to think twice before using the dynamic SQL. So use it wisely and also look for options to avoid it.