I have heard it said you do not want to use Dynamic SQL. Can you give me some concrete or real-life examples?
Personally, I code using it a few times in my database. I think it is OK because of its flexibility.
My guess is about SQL injection or performance. Anything else?
There is nothing wrong with using dynamic SQL if you must. In fact in some circumstances it is the only option that you have. It is more of a recommendation not to use it as yes it can lead to a SQL injection if your input is not sanitized, and yes using dynamic SQL in modules that get called often can be detrimental to it's performance.
I don't think there is a concrete example as per se but I would say this: Try to achieve what you are after using regular queries and statements first - only then once you have exhausted all other avenues do it dynamically. Just remember that executing a dynamic SQL string is done in a separate user session to the module that is calling it - so you may encounter permissions issues where you are not expecting any.
If your worried about performance; test it. If your worried about security; validate your input. There is no right or wrong - only that you use your best judgement based on the information and tools you have available to you at the time.
Dynamic SQL is a tool. And as a tool, it has some applications - for administrative works it is a blessing, for example.
Not so good on SP used by applications, especially if you didn't manage the parameterization of the generated code(latest versions of SQL Server reduced the problems, but still valid).
I won't enter in detail here, so I'll recommend an excellent article on Dynamic SQL issues: The Curse and Blessings of Dynamic SQL by MVP Erland Sommarskog.
It's like most DBMS features, if you use it in the right situation it does it's job well, the wrong situation it does it poorly.
Some things just can't be done without it. Typically I have only found this to be for administrative work, and not application code. Some system commands don't allow for parameters to be used as input.
So for example if I need to run something through a sproc against every database, on many instances with unknown databases, and the command doesn't accept parameters, I usually solve this through dynamic SQL. This is more of a thing in Sybase ASE than MSSQL however.
An excellent application use in Sybase ASE is pivots without knowing the values to be pivoted. This can be done using dynamic SQL in a stored proc, but as far as I know Sybase ASE doesn't support the syntax to do this directly as a query. Only once the values are know can the query be written to pivot the data. - richardcrossley
I won't go much into it, since I think we all know it already, but there can be some risk to SQL injection if it's used incorrectly.
The larger one to me is that the query will be treated like what it is, a unique adhoc query, and not part of the compiled query plan. For something that runs occasionally, no big deal. For something that is executed hundreds of times a minute and that is going to have a lot of unique SQL, it would generate a lot of new, potentially unnecessary, query plans eating up cycles, and shortening the valid time of the plan cache.
