Stored Procedure Component
A Stored Procedure is a subroutine available to applications accessing a relational database system. Typical uses for Stored Procedures are data validation and storing and centralization of logic. If you cannot use Lookup components, Decision components and Data components to do the data handling within a journey, you can use a custom created Stored Procedure.
A Stored Procedure can ask for input values to calculate something (Input parameters) and return the results of its calculations in Output parameters. Values can be passed from the journey to the Stored Procedure for the Input parameters. And the Stored Procedures Output parameter values can be used in the following the journey components or to display on a next page. Just like posted form values, the Stored Procedure's Output values are only available during one request. Until the next page, after this page the data is gone.
A basic example is a poll. A contact can vote what his favorite color is, yellow, green, blue or red. His vote is stored and on the next page the poll results are shown. Storing the contact's vote can easily be done with a Data component. But the poll results cannot be calculated with a default component in the journey. We need to calculate how many votes there are for each poll option and compare it to the total number of votes.
We decided to also store the contact's values with the Stored Procedure and not use an extra Data component before the Stored Procedure component. To do this the Stored Procedure requires some values: the contact's ID, his vote from the previous form, and the list ID of the list to store the contact's vote.
After the calculation, the Stored Procedure returns the total and the value for each poll option. These values have to be displayed on the result page. We can define names for the values in the Stored Procedure component's properties that are used on the next page. E.g. the Value @total is displayed on the result page with ~@total~ (in the Editor)
Events
The component triggers 2 events:
- Onsuccess: when the execution of the procedure is successful
- OnFailed: when the execution of the procedure fails
Properties
General
1. Enter a name and description for the component. The name is shown on the journey canvas.
2. Select the name of the Stored Procedure from the drop down. This procedure is stored in the Selligent database as a Registered Stored Procedure
3. Press 'Load'. Selligent detects automatically the input parameters and the output parameters for this Stored Procedure.
4. If the Stored Procedure changes information of the contact profile, it is necessary to check the option to reload the contact profile after the execution of this Stored Procedure.
Input parameters
The list of input parameters is defined automatically when the Stored Procedure is loaded.
Enter the values that must be assigned to the parameters.
Example:
Possible input parameters are:
* a posted parameters from a form or input component: @VALUE
* a field in the contact profile. E.g. GENDER
* a field in the extended profile. E.g. CONTEST1.ANSWER
* a system value e.g. SYSTEM.LISTID
* string or number. Use single quotes for strings, e.g. 'Spring 20'
* Selligent functions
Output parameters
The output parameters are detected automatically by Selligent Campaign when the Stored Procedure is loaded. There is always at least one parameter stored in the @return_value parameter. This is a numeric value indicating the status of the Stored Procedure.
Technical note:
To be able to use the output parameters, the right column needs to be filled out with the names you want to use in the next step in the journey.
For instance for the @VOUCHER_CODE parameter, @VOUCHER_CODE, @VOUCHERCODE, @VC, are any other name can be filled out. In the next step use the chosen name.
