What is a dynamic SQL query, and when would I want to use one?

What is a dynamic SQL query, and when would I want to us开发者_如何学Ce one? I'm using SQL Server 2005.

---

Here's a few articles:

• Introduction to Dynamic SQL
• Dynamic SQL Beginner's Guide

From Introduction to Dynamic SQL:

Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed. As a result it is a very flexible and powerful tool. You can use dynamic SQL to accomplish tasks such as adding where clauses to a search based on what fields are filled out on a form or to create tables with varying names.

---

Dynamic SQL is SQL generated by the calling program. This can be through an ORM tool, or ad-hoc by concatenating strings. Non-dynamic SQL would be something like a stored procedure, where the SQL to be executed is predefined. Not all DBA's will let you run dynamic SQL against their database due to security concerns.

---

A dynamic SQL query is one that is built as the program is running as opposed to a query that is already (hard-) coded at compile time.

The program in question might be running either on the client or application server (debatable if you'd still call it 'dynamic') or within the database server.