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.
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.
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.
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.
5. Introduction to Dynamic SQL - Part-1 and Part-2
6. Using Dynamic SQL Statements in Stored Procedures
7. Dynamic SQL & SQL injection