Stored procedures are defined in the database and the DPA is configured to call these stored procedures to retrieve the properties immediately before evaluating the conditions and routing the work item.
On the External Properties tab, enable procedures at the activity by placing a check mark in the Enable Stored Procedures check box.
Add a stored procedure to the table either by right-clicking the table and selecting Add or by selecting Edit-Add from the drop-down menu. This will launch the Procedure Editor dialog.
Provide a name for the procedure in the Procedure Name field. Define the parameters of the procedure either by right-clicking and selecting Add or by selecting Parameter and then Add from the drop-down menu.
Repeatedly add parameters until the number of listed parameters matches the number of parameters required for the procedure.
The Order column indicates the order of the parameters from left to right as they appear in the procedure definition within the database.
The Value or Key column contains either the literal value that is to be passed to the IN parameter or the Work Item Key of the dynamic value that is to be passed to the IN, OUT or IN/OUT parameter. If a work item key is specified for an IN value, be sure to wrap the key in percent (%) symbols. Otherwise, the name of the key will be passed as a literal value to the procedure.
The In/Out column indicates the direction of the parameter. This must match the direction specified in the actual procedure implementation within the database. IN means that a value is being passed from EPX into the procedure. OUT means that a value is being returned from the procedure and is to be temporarily stored in a work item property. IN/OUT means that the value of a work item property is to be passed to the stored procedure and then temporarily overwritten with a result that is returned by the procedure. Note that any values returned by an OUT or IN/OUT parameter are not persisted to the EPX database; they are merely evaluated by the DPA and then discarded.
The Data Type column specifies the type of data that you are passing to the procedure or expecting to be returned from the procedure. The available data types are BOOLEAN, STRING, DATE and NUMBER.
Here are some examples:
| Order | Value or Key | In/Out |
|---|---|---|
| 1 | ALiteral |
In |
| 2 | %my.workitem.key% |
In |
| 3 | invalidLiteral |
Out |
| 4 | invalidLiteral2 |
In/Out |
| 5 | %result% |
Out |
| 6 | anotherResult |
Out |
| 7 | %my.other.workitem.key% |
In/Out |
Order 1: The value ALiteral is passed in to parameter 1.
Order 2: The value contained in the work item with key %my.workitem.key% is passed in to parameter 2.
Order 3: invalidLiteral cannot be specified for an OUT parameter.
Order 4: invalidLiteral2 cannot be specified for an IN/OUT parameter.
Order 5: The stored procedure will pass a value out through parameter 5 and this value will be available within the DPA using the work item key result. Note that the DPA creates a temporary work item property to hold the value and this value is not persisted to the EPX database and is therefore not available at subsequent activities. Temporary values created in this way will override any work item properties that arrive at the activity, therefore, if a work item arrives at the DPA and it contains a value whose key is result then the value of result will be obtained from the stored procedure when the DPA condition is evaluated as opposed to from the arriving work item version.
Order 6: The stored procedure will pass a value out through parameter 6 and this value will be available within the DPA using the work item key anotherResult. This illustrates that the % symbols are ignored for OUT values since OUT values are always stored in (temporary) work item properties.
Order 7: The value held in the work item property whose key is my.other.workitem.key will be passed into the stored procedure through parameter 7 and the result will then be temporarily stored in the work item property my.other.workitem.key.
After you have specified the parameter details of the procedure, click OK to close the dialog. The procedure’s details will be displayed in the table. Multiple procedures can be added to the table by following the above steps.
When a work item arrives at this activity, each of the stored procedures will be executed in the order that they appear in the list above (as denoted by the Order column).
Each stored procedure may be individually enabled or disabled by placing or removing the check mark in the Enabled column. The table displays the name of the procedure, a prototype (indicating the data type and direction of each parameter) and the parameter values. You can modify the order in which the procedures are executed by selecting one or more procedures and either right clicking and selecting Move Up or Move Down, or by selecting Edit-Move Up or Edit-Move Down from the drop-down menu.
As each stored procedure is executed in the order specified, the temporary work item properties will accumulate and will be available for each subsequent stored procedure. Note that all work item properties specified as IN or IN/OUT must either exist in work items arriving at the activity or must be created by a previously executed procedure’s OUT parameter (higher up in the table of procedures).
After all of the enabled stored procedures have been executed, the work item property list along with all temporary work item properties (created by the stored procedure OUT values) are passed to the DPA’s evaluation logic. The DPA treats all values as if they had just arrived in a work item, however, any properties returned by Stored Procedures will override work item properties that arrived at the activity.