Dynamic SQL

Definition: Dynamic SQL is a batch of SQL statements that are generated within T-SQL and executed using the Execute (or exes) statement, or via sp_executesql system stored procedure.

Benefits of Dynamic SQL

Predicate Optimization: The real benefit of dynamic SQL is that the execution plans generated for each invocation of the query will be optimized for on the predicates that are actually being used at that moment.  The main issue with the static SQL solutions, aside from maintainability, was that the additional predicates confused the query optimizer, causing it to create inefficient plans.  Dynamic SQL gets around this issue by not including anything extra in the query.

Single Query Plan Caching: For every stored procedure there is one cached query plan and an additional ad hoc plan caches for each invocation of the stored procedure (this can be verified using the view sys.dm_exec_cached_plans).   This means that every time a new argument is passed to the stored procedure, a compilation occurs, which is clearly going to kill performance.  The dynamic query is not being parameterized and is therefore producing duplicate query plans for different arguments.

Drawbacks of Dynamic SQL

Speed: Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan every time at runtime. 

Permissions: Dynamic SQL requires the users to have direct access permissions on all accessed objects like tables and views.  Generally, users are given access to the stored procedures which reference that tables, but not directly on the tables.  In this case, dynamic SQL will not work.

Syntax: One distinct advantage of writing stored T-SQL procedures is that you get a syntax check directly.  With dynamic SQL, a trivial syntax error may not show up until run time.  Even if you test your code carefully, there may be some query, or some variation of a query, that is only run in odd cases and not covered in your test suite.

How to Use Dynamic SQL

In the following example, the @TableName and @ColumnName are dynamic in the SQL select statement.  We first construct a temp table #ResultsTable, assign four records into this temp table.  Then use cursor to read the record one by one from this temp table.  In each loop, the @TableName and @ColumnName are replaced by the TableName and ColumnName in the current record.

 
[crayon-61f1ee6233593592711921/]
Below is a snapshot of what this code snippet looks like in the output window after being dropped into SSMS.