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.
answered Oct 22 '09 at 12:24 AM
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:
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.
answered Oct 12 '09 at 10:30 AM
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:
Some drawbacks may be:
Erlaand does a good job in the article referenced and is usually what I would reference.
answered Oct 12 '09 at 03:54 AM