EXECUTE IMMEDIATE

Syntax:

>>--EXEC SQL--.-------------------.--EXECUTE IMMEDIATE-->
              +--FOR :host_integer---+

 >----:stmt_hvar----END-EXEC-<>

Parameters:

:`host_integer`	A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
`:stmt_hvar`	A character string host variable.

Comments:

Do not use the FOR clause if the EXECUTE IMMEDIATE is part of a DECLARE CURSOR statement.

The EXECUTE IMMEDIATE statement cannot contain input parameter markers or host variables. It cannot return results; any results returned from this statement are discarded. Additionally, the statement cannot contain SQL keywords that pertain exclusively to Embedded SQL.

If any rows are returned, SQLCODE is set to +1.

EXECUTE IMMEDIATE must be used for SET statements specific to the Microsoft SQL Server, that is, those that are intended to execute at that server.

Example:

     EXEC SQL
        DELETE FROM staff WHERE staff_id = 99
     END-EXEC

* Put the required SQL statement in prep.
     MOVE   "insert into staff (staff_id, last_name, first_name ,age,
-           "employment_date) VALUES (99, 'Lee', 'Phillip', 19, '1992-
-           "01-02')" TO prep

* Note EXECUTE IMMEDIATE does not require the statement to be
* prepared
     EXEC SQL
        EXECUTE IMMEDIATE :prep
     END-EXEC

* Check it worked...
     IF SQLCODE = ZERO
        DISPLAY 'Statement executed OK.'
     ELSE
        DISPLAY 'Error: Statement not executed.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF

* Run through the same procedure again, this time deleting the
* values just inserted
     MOVE "delete from staff where staff_id = 99" TO prep
     EXEC SQL
        EXECUTE IMMEDIATE :prep
     END-EXEC

     IF SQLCODE = ZERO
        DISPLAY 'Statement executed OK.'
     ELSE
        DISPLAY 'Error: Statement not executed.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF