Is Dynamic TSQL a Good or Bad Idea?

Erland Sommarskog's article, The Curse and Blessings of Dynamic SQL, is often cited as the canonical reference.

It depends on how you implement dynamic sql what the different benefits and drawbacks are. Do you mean adhoc SQL created on the client or dynamic SQL created in a stored procedure based on parameters passed in? Are you going to use Exec (@sql) or sp_executesql to execute the dynamic SQL?

Some benefits may be:

1. Flexibility - you can return any columns from any table with any where clause, any grouping, and any ordering.
2. Performnance - you can get a better execution plan than doing control of flow and having many different Select's, especially if you use sp_executesql as you get parameterized queries and will more likely get plan re-use.

Some drawbacks may be:

1. Security - depending on your implementation you open yourself to SQL Injection and you may have to grant direct access to the tables.
2. Complexity - it can make your implementation more complex, harder to read and debug. If you are using stored procedures you may choose to use the EXECUTE AS clause or signed modules to keep from having to grant direct table access.
3. Performance - if you do not use sp_executesql then you will likely not benefit from plan re-use and will have more activity in the plan cache as items will be inserted more often and aged out sooner.

Erlaand does a good job in the article referenced and is usually what I would reference.

I think that Dynamic SQL is much maligned. It has a small place in many systems, and shouldn't be a first choice to solve a problem, but I prefer to choose generating dynamic SQL within the database (perhaps in a stored procedure) than allowing it to be used from outside the database. Its use should always be justified.

Because every problem falls into different categories, I would say that the places I have used and justified dynamic SQL are:

Dynamic PIVOTs - the PIVOT construct does not allow for a dynamic number of columns to be generated. In this case, I often generate the PIVOT from the data or metadata in order for the code to require no maintenance.

Table comparison - I have a table/view comparison tool which uses the metadata about two tables/views and creates and executes SQL dynamically to analyze the differences between the tables, including options for what to consider to be keys and thresholds for data differences allowed in numeric columns.

When looping through a variable set of tables, perhaps to append them all to a single table, it is not possible to do this in straight SQL, and generating such dynamic SQL outside the database is overkill, and actually riskier.

A technique I tend to use a lot are code templates:

SET @template = 'SELECT {@COLUMN_LIST} FROM {@TABLE_NAME}'
SET @sql = REPLACE(REPLACE(@template,
    '{@COLUMN_LIST}', @COLUMN_LIST),
    '{@TABLE_NAME}',  @TABLE_NAME)

This allows the SQL to be more readable and maintainable in one place instead of throughout many concatenations.

In all cases, dynamic SQL generated from metadata can be made extremely safe from SQL injection - typically they are only vulnerable if a column name is pathological.

Dynamic SQL is a blessing and curse.

If a limited set of options are available, and inputted values are used as variables where possible, or (where variables won't apply) first checked for validity against system views (such as sys.objects or sys.columns), then it can be quite safe.

If the variety of queries that can be run is large, then the query hint OPTION (RECOMPILE) should be considered, as there is no gain from having the query plan stored in the cache.