Basic SQL instructions are part of the language syntax as static SQL statements, but only a limited number of SQL instructions are supported this way. Dynamic SQL management allows you to execute any kind of SQL statement, hard coded or created at runtime, with or without SQL parameters, returning or not returning a result set.
In order to execute an SQL statement dynamically, you must first prepare the SQL statement to initialize a statement handle, then you execute the prepared statement one or more times:
Figure 1. Dynamic SQL management diagram
When you no longer need the prepared statement, you can free the statement handle to release allocated resources:
Figure 2. FREE statement diagram
When using insert cursors or SQL statements that produce a result set (like SELECT), you must declare a cursor with a prepared statement handle.
Prepared SQL statements can contain SQL parameters by using ? placeholders in the SQL text. In this case, the EXECUTE or OPEN instruction supplies input values in the USING clause.
To increase performance efficiency, you can use the PREPARE instruction, together with an EXECUTE instruction in a loop, to eliminate overhead caused by redundant parsing and optimizing. For example, an UPDATE statement located within a WHILE loop is parsed each time the loop runs. If you prepare the UPDATE statement outside the loop, the statement is parsed only once, eliminating overhead and speeding statement execution.