Adding an SQL procedure

This section describes how to configure a SQL Query node for use with SQL statements.

Adding a procedure using the wizard

You can add a SQL procedure to a SQL Query node. You do this by browsing and selecting existing procedures.

When the procedure is selected then it displays the IN and OUT parameters of the procedure in a tabular column.

To add a SQL procedure, perform these steps:

  1. From the Designer palette, add a SQL Query node or select an existing one.

  2. With the node selected, click the SQL tab on the Properties pane.

  3. Select Add a SQL Procedure and the on the Run SQL Procedure form, click Select.

    You are prompted to select the procedure you want to use.

    When the procedure is selected, the SQL Query Builder form opens. A string containing the procedure text appears in the left side of the form. Input and output parameters appear in a grid format on the right side of the form.

  4. Make changes to the parameters as needed:

    Proc Name

    Display field only. The name of the SQL procedure.

    Parm Name

    Display field only. The name of the parameter.

    Parm Type

    Display field only. Type of parameter. Currently supported:

    • IN

    • OUT

    • INOUT

    • UNKNOWN

    Data Type

    Display field only. Shows the data type of the procedure

    Parm Size

    Display field only. Maximum size that can be stored in the parameter.

    Parm Input

    This field is for specifying input for the IN and INOUT parameters.

    Null values are supported. Specify null for all datatype null values and specify ‘null’ for string null.

    Note: 

    OUT and UNKNOWN parameters do not take input. Data defined in these parameters will result in a warning message and the input will be deleted.

  5. When you are finished selecting parameters, click Finish.

    You will be returned to the Properties pane for specifying a SQL procedure.

  6. On the Properties pane, specify resultsets.

    See the next section for details about how to specify.

Specifying resultsets for a SQL procedure

By default, values for all resultsets retrieved from the procedure (including OUT type param itself) are available to use for the nodes inside the SQL query loop. If you want all results to be available, leave the All radio button as selected.

If multiple resultsets are likely to be returned and you do not want all of them to be available to the SQL query loop, you can select the resultsets you want to use by specifying resultset numbers.

The OUT parameter always includes as the last resultset of the query results. This cannot be changed. For example, if the query returned zero resultsets, the number of resulsets returned is 1 and the OUT parameter will have the resultset number as 1. If the query returned five resultsets, the number of resultsets returned will be six because the last resultset will be the OUT parameter itself.

Following are some examples about how to specify resultsets:

If you specify You will get
1,3

The first and third resultsets (and the final OUT parameter value if the OUT parameter is used).

If two resultsets are retrieved, one containing 150 records and another containing 100 records, the query loop will execute 250 times.
1-5 Resultsets 1-5 (and the final OUT parameter value if the OUT parameter is used).
1,3,5-12 Resultsets 1 and 3 and 5 through 10 (and the final OUT parameter value if the OUT parameter is used).

In all cases, the query loop executes one time for each retrieved record. For example, if two resultsets are retrieved, one containing 150 records and another containing 100 records, the query loop will execute 250 times.

When you specify a resultset, an additional parameter, RESULTSET_NUMBER, is created. This parameter holds the numbers of retrieved resultsets. For example, iif there are 6 datasets totally and if you specify "2,4,6" then the RESULTSET_NUMBER will hold "1,2,3". If you specify "1-5" and only "2" and "4" contain records, RESUTSET_NUMBER will hold "1,2,3,4,5 " but the records in resultsets 1, 3 and 5 will be 0.

Updating a SQL procedure or creating a new SQL procedure manually or through importing

A SQL procedure cannot be selected manually and a procedure selected through the wizard cannot be edited through the SQL procedure input text box.

To add / update manually: On the SQL Query Node properties pane, select the SQL tab and then select the Create/Update SQL Procedure radio button.

To import: On the SQL Query Node properties pane, select the SQL tab and then click the Import button. Locate the file that contains the content you want to import.