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.

Blackmun

How is this "spam" and "not a real question"? It might not be fleshed out, but it's understandable enough to answer. – Boysenberry

+1 for the abuse of down-votes. – Azide

Answer 1

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.

Azide

Another critical, must-read link if you are going to use dynamic SQL:sommarskog.se/dynamic_sql.html – Unrealizable

Answer 2

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.

Iain

Answer 3

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.

Merlynmermaid