I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's 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.
Pros: 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.
Cons: 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.
Do not use Dynamic SQL.
99% of the time Dynamic SQL is used due to the lack of knowledge on how to use optional parameters in stored procedures, the rest 1% of the time is used to create a highly complex query for a report that the customer doesn't understand even. The Curse and Blessings of Dynamic SQL doesn't show an example of why it would be a good idea to use it, instead just suggests that it is problematic because it increases the complexity to debugging, maintaining, not to mention the security risks of SQL Injection, poor performance not because the cache but the bad practices that come with it like using temporary tables and cursors which of course a lazy and naive programmer would abuse of. There is no such thing as the flexibility to write queries that way, SQL is a declarative language and it should be dealt as such.
Laziness is the root of all evil.
Paradoxically this answer is going to rank in the most downvoted ones.