Dynamic SQL

Dynamic SQL
Dynamic SQL describes any SQL DML statement assembled dynamically at runtime as a string and then submitted.

Dynamic SQL is very useful for several tasks:

# Multiple possible query criteria can be dynamically assembled into custom FROM, WHERE and ORDERS BY clauses for flexible queries.
# Code can respond to the schema of the database and generate appropriate triggers, CRUD stored procedures and views.
# Dynamic code can auto-generate very consistent stored procedures.

Drawbacks of Dynamic SQL

# Dynamic SQL that includes user entries in WHERE clauses can be open to SQL injection atacks.
# Poorly written dynamic SQL queries often include extra table references and perform poorly.
# T-SQL code that generates T-SQL code can be tricky to debug.

[Via Microsoft SQL Server 2008 Bible By Paul Nielsen, Uttam Parui]

Below are few awesome resources on Dynamic SQL:

1. Below Dynamic SQL link includes: What is Dynamic SQL, Steps used in processing Dynamic SQL etc.
MSDN

2. Below Dynamic SQL link includes: What is Dynamic SQL?, Dynamic SQL vs. Stored Procedures, When to Use Dynamic SQL?, How we can use Dynamic SQL etc.
Benkotips

3. The Curse and Blessings of Dynamic SQL include: Introducing Dynamic SQL, Dynamic SQL and Stored Procedures, Good Coding Practices and Tips for Dynamic SQL, Common Cases when to (Not) Use Dynamic SQL etc.
Sommarskog

4. Building Dynamic SQL In a Stored Procedure includes: sp_executesql Vs EXECUTE Command, Using Like Operator, IN Operator and Order By In Dynamic SQL etc.
Code Project

5. Introduction to Dynamic SQL - Part-1 and Part-2

6. Using Dynamic SQL Statements in Stored Procedures

7. Dynamic SQL & SQL injection

Although static SQL works well in many situations, there is a class of applications in which the data access cannot be determined in advance. For example, suppose a spreadsheet allows a user to enter a query, which the spreadsheet then sends to the DBMS to retrieve data. The contents of this query obviously cannot be known to the programmer when the spreadsheet program is written.
To solve this problem, the spreadsheet uses a form of embedded SQL called dynamic SQL. Unlike static SQL statements, which are hard-coded in the program, dynamic SQL statements can be built at run time and placed in a string host variable. They are then sent to the DBMS for processing. Because the DBMS must generate an access plan at run time for dynamic SQL statements, dynamic SQL is generally slower than static SQL. When a program containing dynamic SQL statements is compiled, the dynamic SQL statements are not stripped from the program, as in static SQL. Instead, they are replaced by a function call that passes the statement to the DBMS; static SQL statements in the same program are treated normally.
The simplest way to execute a dynamic SQL statement is with an EXECUTE IMMEDIATE statement. This statement passes the SQL statement to the DBMS for compilation and execution.
One disadvantage of the EXECUTE IMMEDIATE statement is that the DBMS must go through each of the five steps of processing an SQL statement each time the statement is executed. The overhead involved in this process can be significant if many statements are executed dynamically, and it is wasteful if those statements are similar. To address this situation, dynamic SQL offers an optimized form of execution called prepared execution, which uses the following steps:

1. The program constructs an SQL statement in a buffer, just as it does for the EXECUTE IMMEDIATE statement. Instead of host variables, a question mark (?) can be substituted for a constant anywhere in the statement text to indicate that a value for the constant will be supplied later. The question mark is called as a parameter marker.
2. The program passes the SQL statement to the DBMS with a PREPARE statement, which requests that the DBMS parse, validate, and optimize the statement and generate an execution plan for it. The program then uses an EXECUTE statement (not an EXECUTE IMMEDIATE statement) to execute the PREPARE statement at a later time. It passes parameter values for the statement through a special data structure called the SQL Data Area or SQLDA.
3. The program can use the EXECUTE statement repeatedly, supplying different parameter values each time the dynamic statement is executed.

Prepared execution is still not the same as static SQL. In static SQL, the first four steps of processing an SQL statement take place at compile time. In prepared execution, these steps still take place at run time, but they are performed only once; execution of the plan takes place only when EXECUTE is called. This helps eliminate some of the performance disadvantages inherent in the architecture of dynamic SQL. The next illustration shows the differences between static SQL, dynamic SQL with immediate execution, and dynamic SQL with prepared execution.

Show: Inherited Protected