Dynamic SQL is part of ANSI and the ISO SQL2 standard. It is useful for running an interactive application. If the application only accepts a small set of SQL statements, you can embed them within the program. However, if the application accepts many types of SQL statements, you can benefit from constructing SQL statements, then binding and executing them dynamically.
The following situation would benefit from use of dynamic SQL: The application program searches a bookseller’s database of books for sale. A potential buyer can apply many criteria, including price, subject matter, type of binding, number of pages, publication date, language, and so on.
A customer might say, “I want a nonfiction book about business that costs between $10 and $20.” This request is readily expressed as a Transact-SQL statement:
select * from titles where
type = "business"
and price between $10 and $20
It is not possible to anticipate the combinations of criteria that all buyers will apply to their book searches. Therefore, without using dynamic SQL, an Embedded SQL program could not easily generate a list of prospective books with a single query.
With dynamic SQL, the bookseller can enter a query with a different where clause search condition for each buyer. The seller can vary requests based on the publication date, book category, and other data, and can vary the columns to be displayed. For example:
select * from titles
where type = ?
and price between ? and ?
The question marks (“?”) are dynamic parameter markers that represent places where the user can enter search values.
