Dynamic SQL Format 2
Description
Use this format to execute a SQL statement that does not produce a
result set but does require input parameters. You can use this format to
execute all forms of Data Definition Language (DDL).
Syntax
|
1 2 3 |
PREPARE DynamicStagingArea FROM SQLStatement {USING TransactionObject} ; EXECUTE DynamicStagingArea USING {ParameterList} ; |
|
Parameter |
Description |
|---|---|
|
DynamicStagingArea |
The name of the DynamicStagingArea (usually If you need a DynamicStagingArea variable |
|
SQLStatement |
A string containing a valid SQL statement. The Enter a question mark (?) for each |
|
TransactionObject (optional) |
The name of the transaction object that identifies |
|
ParameterList (optional) |
A comma-separated list of PowerScript variables. |
Usage
To specify a null value, use the SetNull function.
Examples
These statements prepare a DELETE statement with one parameter in
SQLSA and then execute it using the value of the PowerScript variable
Emp_id_var:
|
1 2 3 4 |
INT Emp_id_var = 56 PREPARE SQLSA FROM "DELETE FROM employee WHERE emp_id=?" ; EXECUTE SQLSA USING :Emp_id_var ; |
These statements prepare an INSERT statement with three parameters
in SQLSA and then execute it using the value of the PowerScript
variables Dept_id_var, Dept_name_var, and Mgr_id_var (note that
Mgr_id_var is null):
|
1 2 3 4 5 6 7 8 9 |
INT Dept_id_var = 156 INT Mgr_id_var String Dept_name_var Dept_name_var = "Department" SetNull(Mgr_id_var) PREPARE SQLSA FROM "INSERT INTO department VALUES (?,?,?)" ; EXECUTE SQLSA USING :Dept_id_var,:Dept_name_var,:Mgr_id_var ; |