Chapter 29. Dynamic SQL and Code Generation


  • 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 ...

Get Microsoft® SQL Server® 2008 Bible now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.