Temp-table code technique

The following example shows the initial explanation that the stored procedure myProc is comprised of two fields—one is an integer and the other is a character, and a result set.

SELECT "myProc," cust_num, cust_name FROM anytablename

Basic execution of a stored procedure using LOAD-RESULT-INTO functionality

The following example shows the basics of executing a call to a stored procedure using the LOAD-RESULT-INTO functionality. Note that in this example, PROC-STATUS function is not specified. Therefore, there is no possibility of a return value.

/* Calling a stored procedure, using the LOAD-RESULT-INTO phrase*/

DEFINE VARIABLE res    AS INTEGER NO-UNDO.
DEFINE VARIABLE ttHndl AS HANDLE  NO-UNDO.

DEFINE TEMP-TABLE ttl
  FIELD f1 AS INTEGER
  FIELD f2 AS CHARACTER.

ttHndl = TEMP-TABLE tt:HANDLE.

RUN STORED-PROC myProc LOAD-RESULT-INTO ttHndl (INPUT 1, OUTPUT res).

Using an existing dynamic temp-table without the TEMP-TABLE-PREPARE ( ) method

The following example shows the basics of using an existing dynamic temp-table without the TEMP-TABLE-PREPARE () method. In this instance, send-sql-statement is used rather than a predefined stored proc. In contrast, the third example code that appears later in this section shows the same approach, but explicitly defines the existing dynamic temp-table with the TEMP-TABLE-PREPARE() method.

/* Calling a stored procedure, using an existing temp-table without temp table prepare */

DEFINE VARIABLE ttHndl AS HANDLE NO-UNDO.

CREATE TEMP-TABLE ttHndl.

RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO ttHndl 
  ("select * from customer").

Using an existing dynamic temp-table with the TEMP-TABLE-PREPARE ( ) method

the following example shows the basics of using an existing dynamic temp-table with the TEMP-TABLE-PREPARE() method.

/* Calling a stored procedure, using an existing temp-table with temp table prepare*/

DEFINE VARIABLE ttHndl AS HANDLE NO-UNDO.

CREATE TEMP-TABLE tt1Hndl.
ttHndl:ADD-NEW-FIELD("custNum", "integer").
ttHndl:ADD-NEW-FIELD("name", "character").
ttHndl:TEMP-TABLE-PREPARE("ordX").

RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO ttHndl 
  ("select custNum,name from myDB.customer").

Note the following points about this example:

• As a prerequisite for creating the code shown, the developer would need to define the schema for the table.
• Once the temp-table schema begins preparation from the clear state, the temp-table must be defined to the exact specifications for the result sets as generated by the RUN STORED-PROC statement or send-sql-statement. Otherwise, the RUN STORED-PROC will end in failure. Also, note the TEMP-TABLE-PREPARE must be called at the completion of the temp-table definition associated with the stored procedure results.

Calling a stored procedure and using the EXTENT phrase

The following example shows passing the handles of empty dynamic temp-tables to enable a MS SQL Server DataServer to create schema based on the result-set schema.

/ *Calling a stored procedure, and using the EXTENT phrase to pass handles of empty temp-tables to enable the MS SQL DataServer to create schema holder definitions based on each temp-table's result-set schema. */

DEFINE VARIABLE ttCustomer AS HANDLE NO-UNDO.
DEFINE VARIABLE ttHandle   AS HANDLE NO-UNDO EXTENT 2.
DEFINE VARIABLE ttOrder    AS HANDLE NO-UNDO.

CREATE TEMP-TABLE ttCustomer.
CREATE TEMP-TABLE ttOrder.
ttHand1e[1] = ttCustomer.
ttHand1e[2] = ttOrder.

RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO ttHandle
  ("Select * from customer; select * from order").

Calling an enhanced stored procedure with a single temp table and the send-sql-statement

The following example shows how to use the enhanced stored procedure syntax with a single static temp-table and the send-sql-statement.

/* Calling an enhance stored procedure with a single temp table and the send-sql-statement */

DEFINE VARIABLE ttHandle AS HANDLE NO-UNDO.

DEFINE TEMP-TABLE ttHandle
  FIELD f1 AS INTEGER
  FIELD f2 AS CHARACTER.

ttHandle = TEMP-TABLE ttHandle:HANDLE.

RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO ttHandle 
  ("Select cust_num,name from customer").

Use of a PROC-STATUS phrase

The following example shows the use of a PROC-STATUS phrase. The PROC-STATUS phrase must be defined as part of the RUN-STORED-PROC statement because of the implicit CLOSE STORED-PROC that is associated with the LOAD-RESULT-INTO phrase.

/* Sixth example - Example of the implicit close stored-proc and use of LOAD-RESULT-INTO */

DEFINE VARIABLE iStat    AS INTEGER NO-UNDO.
DEFINE VARIABLE ttHandle AS HANDLE  NO-UNDO.

CREATE TEMP-TABLE ttHandle.

RUN STORED-PROCEDURE pcust LOAD-RESULT-INTO ttHandle
  iStat = PROC-STATUS (20, output 0, output 0). 

DISPLAY iStat.

Note that the PROC-STATUS phrase does not need a PROC-HANDLE phrase because it is retrieved using the RUN STORED-PROC statement and not after this statement's execution as it typically is used.