Details about a dynamic temp-table in a prepared state

The following example shows multiple dynamic temp-tables in a prepared state. The temp tables, tt1 and tt2, are prepared in the code just before the RUN STORED-PROC statement is called and the temp-tables are passed. The temp-table definition is considered prepared because of the explicit inclusion of each field name and associated data type.

Multiple temp-tables in a prepared state

/* example - Multiple dynamic temp-tables in a prepared state*/

DEFINE VARIABLE tt1      AS HANDLE NO-UNDO.
DEFINE VARIABLE tt2      AS HANDLE NO-UNDO.
DEFINE VARIABLE tt-array AS HANDLE NO-UNDO EXTENT 2.

CREATE TEMP-TABLE tt1.
tt1:ADD-NEW-FIELD("custnum", "integer").
tt1:ADD-NEW-FIELD("name", "character").
/*no more fields will be added */
tt1:TEMP-TABLE-PREPARE ("ordx1").

CREATE TEMP-TABLE tt2.
tt2:ADD-NEW-FIELD("ordernum", "integer").
tt2:ADD-NEW-FIELD("orderdate", "date").
/*no more fields will be added */
tt2:TEMP-TABLE-PREPARE ("ordx2").

ASSIGN
  tt-array[1] = tt1
  tt-array[2] = tt2.

RUN STORED-PROC myproc LOAD-RESULT-INTO tt-array (INPUT 10, INPUT "order").

In the dynamic temp-table example above, note that the temp-table definitions must match the result set that the send-sql-statement returns. For example, the number of fields and the data type of each field in the result must have a corresponding field defined in the temp-table that will receive the result set. No mapping exists between the result set and the temp-table. Therefore, the first field defined in the temp-table corresponds to the first column of the result set and so forth.