Configuring an advanced stored procedure database lookup

Note: If there are multiple stored procedures defined in the DB Lookup table stored procedure, then only the first stored procedure is run in the engine. This applies even though you can add multiple stored procedures through the GUI.

To configure an advanced stored procedure database lookup:

  1. Select Advanced Database Lookup.
  2. Open the Database Connection menu and select a database. The list includes all configured database connections, including master (primary) site connections. This is required.
  3. For Type, select Stored Procedure.
  4. In the Stored Procedures field, you can directly specify any valid query statement. You can also click Configure to open the Stored Procedure Configurator dialog box where you can compose the query statement. This is required.
    This dialog box provides functions to help you compose the stored procedure.
    In the Stored Procedure Configurator dialog box, all real condition values must be replaced with the appropriate placeholder. Stored procedure content can span multiple lines and must be a query statement. Otherwise, the GUI prompts an error message.
  5. Select the appropriate Procedure, Input Parameters, and Output Parameters.
    The ORACLE database supports defining a result set using Output Parameters.
    Prefix codes for Output Parameters are:
    • _CLRC_: Return code.
    • Parameter name and "OUT": Out parameter.
    • RS_ and the Parameter name: Manually specify this in the text area.
  6. If you select Return code, then a ?= is placed at the beginning of the SQL statement. The _CLRC_ variable can receive a value from runtime.
    If required, then you can select a parameter to modify from the Input or Output list.
  7. When finished, select OK.