Microsoft® SQL Server® 2008 Bible

Chapter 29. Dynamic SQL and Code Generation

IN THIS CHAPTER

• Executing dynamic SQL

• Parameterized queries

• The risk of SQL injection

• Generating stored procedures

• Alternatives to dynamic SQL

Folks laugh when they hear that my favorite project is based on the notion that T-SQL is a great language for code generation. Nordic (New Object/Relational Design) is essentially a code-generation tool that uses dynamic SQL to create tables, stored procedures, and views. T-SQL works rather well for code generation, thank you.

The term dynamic SQL has a couple of differing definitions. Some say it describes any SQL query submitted by a client other than a stored procedure. That's not true. SQL submitted from the client is better known as ad-hoc SQL.

It's more accurate to say that 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 ORDER 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.

However, note the following issues when developing dynamic SQL:

• Dynamic SQL that includes user entries in WHERE clauses can be open to SQL injection attacks.

• Poorly written dynamic SQL queries often include extra table references and perform poorly.

• T-SQL code that generates T-SQL ...