O'Reilly logo

Microsoft® SQL Server® 2008 Bible by Uttam Parui, Mike White, Paul Nielsen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required