SO has plenty of Q&As about how to accomplish various tasks using dynamic SQL, frequently the responses are accompanied with warnings and disclaimers about the advisability of actually using the approach provided. I've worked in environments ranging from "knowing how to use a cursor is a bad sign" to "isn't sp_executesql neat!".
When it comes to production systems should dynamic sql always be avoided or should it have a valid place in the programming toolbox. If not/so, Why?
My previous shop would never allow such things to execute against the database (SQL Server). It was prohibited as practice, and the DB was locked down to prevent it. All work goes through objects (SPs, etc).
This is the right way to do it always, IMHO.
Answers to some of your questions can be found here The Curse and Blessings of Dynamic SQL
Sometimes you have to use dynamic SQL because it will perform better
It depends on what you mean by dynamic sql. Queries built where parameter values substituted directly into the sql string are dangerous and should be avoided except for the very rare case where there is no other option.
Dynamic SQL using the appropriate parameterized query mechanism supported by your platform can be very powerful.
There are edge cases where dynamic SQL is both easier and faster than the alternative. As long as you keep them few and far between and they are dynamically generated prepared parameterized SQL I see no big problem with them.
The cost of 'dynamic SQL' varies between DBMS.
In IBM DB2, where query plans can be pre-compiled down to machine code, the cost of Dynamic SQL is significant.
In IBM Informix (IDS - Informix Dynamic Server), most queries are effectively 'dynamic SQL' (the exception is queries in stored procedures) in that the SQL is interpreted at run-time, even though the client-side notation uses static SQL.
I believe - though a DB2 expert might be able to contradict me - that the CLI (ODBC, aka CCC) and JCC (JDBC) systems for DB2 do all SQL as dynamic SQL.
I don't know what Oracle, Sybase, MS SQL Server do - my suspicion is that they hew closer to the line adopted by IDS than the line adopted by DB2. For MySQL and PostgreSQL, I'd be surprised if they did not behave more like IDS than DB2.
As a result, with IDS, there is no particular overhead to using Dynamic SQL; at the server level, your SQL is dynamic anyway. Other DBMS may have other factors at play.
One issue for all servers is 'how do you identify the pre-compiled query from the SQL sent over the wire'. With DB2, the pre-compilers identify the package which is used, and the communications protocol between application and server identifies that. My understanding is that the DB2 clients such as ODBC and JDBC do not use a pre-compiled package - hence I think that they effectively do dynamic SQL all the time.
Beware SQL injection with Dynamic SQL!
Dynamic SQL has a place - even in production code. Executing arbitrary code with security holes does not.
In general, I would avoid using dynamic SQL if there is not a good reason to use it. Dynamic SQL does not get checked until it runs, so you obviously have a bigger testing burden. However, there are plenty of good times to use it when dealing with administrative tasks, static code-generation, accommodating changing systems without excessive maintenance based on querying metadata, using DRY to avoid redundancy, etc.
There are several considerations when evaluating the use of dynamic SQL:
Remember, SQL is code, and an RDBMS is another API. It is NOT special, or at least not much; deal with it like you would any other code and API. In particular, don't just code directly against the API: modularize your code and write some helper methods to make it easier and reusable.
Our system has what I think is a lot of dynamic SQL in it, mostly because we have a lot of dynamically created objects (tables, indexes, views mostly). A lot of this is legacy; things like partitioned tables in 2k5 help somewhat in some of our use cases. But as mentioned you need to make a good case for it; on-the-fly SQL inside the procs usually has a better (static) solution.