Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour 22. Advanced SQL Topics
Dynamic SQL allows a programmer or end user to create a SQL statement's specifics at runtime and pass the statement to the database. The database then returns data into the program variables , which are bound at SQL runtime.
To comprehend dynamic SQL, review static SQL. Static SQL is what this book has discussed thus far. A static SQL statement is written and not meant to be changed. Although static SQL statements can be stored as files ready to be executed later or as stored procedures in the database, static SQL does not quite offer the flexibility that is allowed with dynamic SQL.
The problem with static SQL is that even though numerous queries may be available to the end user, there is a good chance that none of these "canned" queries will satisfy the users' needs on every occasion. Dynamic SQL is often used by ad hoc query tools, which allow a SQL statement to be created on-the-fly by a user to satisfy the particular query requirements for that particular situation. After the statement is customized according to the user's needs, the statement is sent to the database, checked for syntax errors and privileges required to execute the statement, and compiled in the database where the statement is carried out by the database server. Dynamic SQL can be created by using call-level interface, which is explained in the next section.
Although dynamic SQL provides more flexibility for the end user's query needs, the performance may not compare to that of a stored procedure whose code has already been analyzed by the SQL optimizer.
