IN THIS CHAPTER
Executing dynamic SQL
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 ...