Dynamic SQL Format 2 SQL statement
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 |
PREPARE <span>DynamicStagingArea</span> FROM <span>SQLStatement</span> <br> {USING <span>TransactionObject</span>} ; |
|
1 |
EXECUTE <span>DynamicStagingArea</span> USING {<span>ParameterList</span>} ; |
|
Parameter |
Description |
|---|---|
|
DynamicStagingArea |
The name of the DynamicStagingArea (usually SQLSA). If you need a DynamicStagingArea variable other than SQLSA, you must declare it and |
|
SQLStatement |
A string containing a valid SQL statement. The string can be a Enter a question mark (?) for each parameter in the statement. Value |
|
TransactionObject (optional) |
The name of the transaction object that |
|
ParameterList |
A comma-separated list of PowerScript |
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 |
INT Emp_id_var = 56 |
|
1 |
PREPARE SQLSA |
|
1 |
FROM "DELETE FROM employee WHERE emp_id=?" ; |
|
1 |
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 |
INT Dept_id_var = 156 |
|
1 |
INT Mgr_id_var |
|
1 |
String Dept_name_var |
|
1 |
Dept_name_var = "Department" |
|
1 |
SetNull(Mgr_id_var) |
|
1 |
PREPARE SQLSA |
|
1 |
FROM "INSERT INTO department VALUES (?,?,?)" ; |
|
1 |
EXECUTE SQLSA |
|
1 |
      USING :Dept_id_var,:Dept_name_var,:Mgr_id_var ; |