I use Dynamic SQL queries for everything.. yes even for buttering my bread and putting on my socks.
Yes, there are some security risks when using Dynamic SQL, but I know the risks and manage the implementation on code very carefully.
In most instances this relates to script I run to manage stuff or reports.
Where to begin:
1. Erland Sommarskog, dude you have made my life so much easier with this post. I keep it open in my browser at all times. The depth of information with regards to Dynamic SQL is really something special. Thanks Erland http://www.sommarskog.se/dynamic_sql.html
2. Pinal Dave, I cannot even begin to describe how much I have learned from Mr Pinal Dave on http://blog.sqlauthority.com. Thank you so much. http://blog.sqlauthority.com/tag/dynamic-sql/
There are a lot of things about Dynamic SQL that people do not know about when they start writing some fancy procedures and end up wrecking everything on the server.
Some of the considerations you need to think about as your coding skills improve in SQL and especially Dynamic SQL are: