Using Dynamic SQL › Dynamic Insert, Update, and Delete Operations › Using EXECUTE IMMEDIATE

Using EXECUTE IMMEDIATE

When to Use It

Use EXECUTE IMMEDIATE to dynamically compile and execute a statement that will be issued only once in the transaction.

If a program consists mainly of dynamic SQL statements, consider using EXECUTE IMMEDIATE for the few remaining SQL statements. You can precompile the program with the NOINSTALL option, eliminating an RCM and an access module to execute the program. This may be more efficient in your processing environment.

EXECUTE IMMEDIATE example

In this example, the program builds an INSERT statement in working storage and moves the complete statement to a host variable, STATEMENT-TEXT. The program issues an EXECUTE IMMEDIATE statement on the text contained in the host variable:

 DATA DIVISION.
 WORKING-STORAGE SECTION.

 01   INSERT-STATEMENT-TEXT.
      02 FILLER            PIC X(21)  VALUE
         "INSERT INTO C_DIVISION VALUES ('".
      02 DIV-CODE-TEXT     PIC X(3).
      02 FILLER            PIC X(3)  VALUE
         "','".
      02 DIV-NAME-TEXT     PIC X(40).
      02 FILLER            PIC X(2)  VALUE
         "',".
      02 DIV-HEAD-ID-TEXT  PIC X(4).
      02 FILLER            PIC X(3)  VALUE
         ")".

 EXEC SQL BEGIN DECLARE SECTION END-EXEC.
 01   STATEMENT-TEXT    PIC X(76).
 EXEC SQL END DECLARE SECTION   END-EXEC.


 PROCEDURE DIVISION.

 MOVE INPUT-DIV-CODE TO DIV-CODE-TEXT.
 MOVE INPUT-DIV-NAME TO DIV-NAME-TEXT.
 MOVE INPUT-DIV-HEAD-ID TO DIV-HEAD-ID-TEXT.
 MOVE INSERT-STATEMENT-TEXT TO STATEMENT-TEXT.

 EXEC SQL
 EXECUTE IMMEDIATE :STATEMENT-TEXT
 END-EXEC.

Error-checking

There is no error-checking technique that is specific to EXECUTE IMMEDIATE. Check for SQLCODE < 0, or check for a specific SQLSTATE value if appropriate.