The Curse and Blessings of Dynamic SQL

An SQL text by Erland Sommarskog, SQL Server MVP.
If you follow the various newsgroups on Microsoft SQL Server, you often see people asking why they can't do:
SELECT * FROM @tablename
SELECT @colname FROM tbl
SELECT * FROM tbl WHERE x IN (@list)
For all three examples you can expect someone to answer Use dynamic SQL and give a quick example on how to do it. Unfortunately, for all three examples above, dynamic SQL is a poor solution. On the other hand, there are situations where dynamic SQL is the best or only way to go.
In this article I will discuss the use of dynamic SQL in stored procedures and to a minor extent from client languages. To set the scene, I start with a very quick overview on application architecture for data access. I then proceed to describe the feature dynamic SQL as such, with a quick introduction followed by the gory syntax details. Next, I continue with a discussion on SQL injection, a security issue that you absolutely must have good understanding of when you work with dynamic SQL. This is followed by a section where I discuss why we use stored procedures, and how that is affected by the use of dynamic SQL. I carryon with a section on good practices and tips for writing dynamic SQL. I conclude by reviewing a number of situations where you could use dynamic SQL and whether it is a good or bad idea to do it.
The article covers all versions of SQL Server from SQL 6.5 to SQL 2005, with emphasis on SQL 2000 and SQL 2005.
Contents:
Accessing a Data from an Application
Introducing Dynamic SQL
A First Encounter
sp_executesql
EXEC()
SQL Injection – a Serious Security Issue
Dynamic SQL and Stored Procedures
The Permission System
Caching Query Plans
Reducing Network Traffic
Encapsulating Logic
Keeping Track of what Is Used
Easiness of Writing SQL Code
Addressing Bugs and Problems
Good Coding Practices and Tips for Dynamic SQL
Use Debug Prints!
Nested Strings
Spacing and Formatting
Dealing with Dynamic Table and Column Names
Quotename, Nested Strings and Quotestring
QUOTED_IDENTIFIER
sp_executesql and Long SQL Strings in SQL 2000
Dynamic SQL in User-Defined Functions
Cursors and Dynamic SQL
EXEC() at Linked Server
Common Cases when to (Not) Use Dynamic SQL
SELECT * FROM @tablename
SELECT * FROM sales + @yymm
UPDATE tbl SET @colname = @value WHERE keycol = @keyval
SELECT * FROM @dbname + '..tbl'
SELECT * FROM tbl WHERE col IN (@list)
SELECT * FROM tbl WHERE @condition
Dynamic Search Conditions
SELECT * FROM tbl ORDER BY @col
SELECT TOP @n FROM tbl
CREATE TABLE @tbl
CREATE TABLE with Unknown Columns
Linked Servers
OPENQUERY
Dynamic Column Widths
Dynamic SQL and Maintenance Tasks
Acknowledgements and Feedback
Revision History
Note: many of the code samples in this text works against the pubs and Northwind databases that ship with SQL 2000 and SQL 7, but not with SQL 2005. You can download these databases from Microsoft's web site.