SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
D-81
Features of Dynamic SQL

Flexibility to construct SQL statements at run time: for example, an interactive
interface that is similar to SQLCI, but is designed for an inexperienced user.

Restriction of access to data in a table: for example, the program might code an
UPDATE statement for certain columns in a table, but allow the user to enter any
selection criteria (WHERE clause) at run time.

Client-server support with deferral of definition of SQL statements until run time: for
example, when the user of an application on a personal computer wants to
manipulate data in a NonStop SQL/MP database on a host system. Such a
program cannot use SQLCI. The user formulates an SQL statement on the
personal computer and the application sends it to a server process on the system
over Multilan or another communications protocol.
If you plan to execute a dynamic SQL statement only once, you can use EXECUTE
IMMEDIATE to execute the statement, and save any memory that would have stored
the execution plan.
Features of Dynamic SQL
When you write a program that uses dynamic SQL, you use many of the same SQL
statements as you would in static SQL. You can perform most of the same operations
using dynamic SQL statements that you perform with static SQL statements. You can
use DDL, DML, and DCL statements in both modes.
The difference is that all or part of a dynamic SQL statement is obtained at run time
from the user, or generated by your program. You program stores the statement in a
character host variable, and then compiles and executes it. With dynamic SQL
statements, you must perform some additional operations (such as building descriptors
for host variables) that are performed for you when you use static SQL statements.
After compilation, SQL executes statements in the same way, whether they are
dynamic or static. SQL places the results of dynamic SQL statements into output
variables; you can use DESCRIBE to obtain information about those variables.
For information about dynamic SQL, see the SQL/MP programming manual for your
host languages.