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