EXECUTE IMMEDIATE

Syntax

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

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

Parameters

:row_count An integer host variable that specifies the number of rows to be used if the result and parameter host variables are all arrays of the same size, and not all elements should be used. You cannot use the FOR clause if the EXECUTE IMMEDIATE is part of a DECLARE CURSOR statement.
:stmt_hvar A character string host variable.

Purpose

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