Understanding dynamic SQL

This is an introduction to dynamic SQL programming.

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 EXECUTE the prepared statement one or more times:

When you no longer need the prepared statement, you can FREE the statement handle to release allocated resources:

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.

Note: To increase performance efficiency of SQL executed in a loop, use PREPARE outside the loop, together with EXECUTE inside the loop, to eliminate overhead caused by redundant parsing and optimizing.

The EXECUTE IMMEDIATE instruction prepares and executes an SQL statement in a single instruction. SQL parameters and result sets cannot be used with EXECUTE IMMEDIATE.

The base.SQLHandle built-in class is a 3GL API to execute SQL statements dynamically and perform SQL introspection (to get result set column types).