The Add Parameter task allows you to define input, output, and return parameters values for a Stored Procedure. It is only enabled if you set the Query Type as Stored Procedure (it replaces the Add Where task).
Clicking the Add Parameter task opens the Parameter Property panel. This panel includes the following functionality:
A drop-down list containing the direction of the parameter. The direction must match the direction of the corresponding parameter in the stored procedure. You can select one of the following values from the drop-down list:
Input
Output
Input/Output
Return Value
A drop-down list containing the SQL type of the parameter. It must match the type of the corresponding parameter in the Stored Procedure. You can select one of the following values from the drop-down list:
INTEGER
FLOAT
VARCHAR
CURSOR
DATE
TIMESTAMP
BIGINT
SMALLINT
DOUBLE
REAL
DECIMAL
TIME
Note: Genesys Agent Scripting partially supports mapping the DB2 BIGINT data type in the Database Interface. It is supported with the following exceptions.
Cannot create a Database Interface with a Where clause referencing a BIGINT column.
Cannot create a Database Interface that performs an Insert or Update specifying a value for a BIGINT column.
This is the name of the parameter specified in the Stored Procedure.
A drop-down list containing the type of the value. If the direction is an input then this field defines whether the input value of the parameter is either a specific value or is the contents of a specific field. If the direction is an output or return value, then the Value Type must be Field in order to define which field is to contain the output/return parameter value. This field is disabled for a parameter whose SQL Type is set to CURSOR. An output CURSOR parameter can be saved to a Table field by adding a Database Table and its associate columns to the Database Interface, matching results from the cursor, and mapping it to a Genesys Agent Scripting Field of type Table.
This field displays when you select Field as the Value Type. Select the field whose content is to be passed as an input parameter or the field that is to contain the value of an output/return parameter.
This field appears if one selects Value as the Value Type. In this field you can set a hard-coded value for an input parameter.
Although generally, Genesys Agent Scripting supports calls to Stored Procedures, the following restrictions apply:
Input cursor parameters are not supported.
Only one output cursor parameter can be defined.
For Database provider System.Data.SqlClient and System.Data.OracleClient, all input and output parameters must be defined with parameter names.
For Database provider System.Data.OracleClient, a returned cursor parameter is not supported.
For OLEDB access to Oracle prior to version 9.2.0.4.0, timestamp parameters are not supported.
The cursor output/returned parameter is only supported for the Oracle database.