Classes of Use

There are four classes of use for dynamic SQL:

When no SELECT statements are issued dynamically, dynamic allocation of main storage is not needed. This is the simplest way to use dynamic SQL (see the example on Dynamic SQL for Non-SELECT Statements).
Use fixed-list SELECT statements when you know ahead of program execution time what kinds of host variables need to be declared to store results. That is, when you have rows that contain a known number of values of a known type, use fixed-list SELECT statements to return them (see the example on Dynamic SQL for Fixed-List SELECT Statements).
Use varying-list SELECT statements when you do not know ahead of program execution time what kinds of host variables need to be declared to store results. That is, when you have rows that contain an unknown number of values of unknown type, use varying-list SELECT statements to return them (see the example on Dynamic SQL for Varying-List SELECT Statements).
If you need to have several kinds of dynamic SQL statements (including varying-list SELECT statements, in any of which a variable number of parameter markers might be contained) executed in a program, the program could be said to execute "arbitrary" SQL statements. An example begins on Dynamic SQL for Arbitrary Statement-Types.

Note: In addition to the examples in the following sections, see the sample dynamic SQL program on the CA Datacom/DB eSupport website.