Immediately executes the SQL statement.
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.
|
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
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.