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

Question

What is a dynamic SQL query, and when would I want to use one? I'm using SQL Server 2005.

Answer 1

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

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

Answer 2

Here's a few articles:

• Intro to Dynamic SQL
• Dynamic SQL Beginners Guide

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

Answer 3

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.