Prepared Parameters
If you want to replace static names in the SQL statement with process variables, you must edit the SQL Statement field and replace the variable items with a question mark (?). For example, if you want to specify a variable for the first column returned, you would specify the following SQL statement:
SELECT DEMO.ITEM.ITEM_ID FROM DEMO.ITEM
WHERE DEMO.ITEM.ITEM_ID = ?
The ?
replaces the value in the WHERE clause, and you can then provide input to the SQL statement from process data on the Input tab.
Keep in mind,
?
is not valid SQL syntax, so checking the syntax of a query that contains question marks can return an invalid character error in the Query Designer wizard. When you add or remove question marks in SQL statements, a warning appears to the right of the Prepared Parameters field ("SQL/ Prepared Parameter Count Mismatch") to alert you to add or remove corresponding prepared parameters. Each prepared parameter corresponds to the question mark in the same position in the SQL statement. That is, the first prepared parameter in the list corresponds to the first question mark, the second prepared parameter in the list corresponds to the second question mark, and so on. The warning is for informational purposes only, you must make sure the parameters in this field correctly correspond to the SQL statement.
You can optionally specify names for each prepared parameter. By default, the prepared parameters are named Prepared_Param_1
, Prepared_Param_2
, and so on.
You must supply the datatype of each parameter to the SQL statement, and this datatype is used in the input schema for the statement. You can use the up or down arrow keys to move a specified datatype to the previous or next parameter. You can also select a contiguous set of parameters to move them up or down, or delete them with the delete button.
At run time, any parameters you place in the SQL statement are replaced with the mapped values for those parameters on the Input tab.