Database Action – Stored Procedure

Use the Edit Stored Procedure dialog to run one or more SQL statements that were previously created and stored as a procedure.

When you pass values to a standard database action such as this one, do not use the ToSql() function to convert the values. IPA Designer does any required conversion automatically. If you use ToSql() with standard database actions, it causes an error.

Note: If your stored procedure returns a result set, you must define a custom type to hold the result set before you configure the Stored Procedure database action. Column names and types in the database must match the names and types of the custom type members. If they do not match, the action will fail.

To access CIC Data Sources in your process designs, you must have the user rights to access them. If you get an error message when you try to access a data source from a process design, contact your system administrator to obtain the required user rights. Users of the process must also have rights to access the data sources.

To configure a call to a stored procedure:

1. In the Execute this Procedure list box, select the procedure to call.

2. The Parameters tab will display parameters for the procedure you select.

3. On the Parameters tab, enter input values and a return value (see figure).
   
   

4. If the procedure should return a result set, then at the bottom of the Parameters tab (see figure):
   
   

   1. Select the Operation will return a result set check box.

   2. In the Result set type list box, select the result set type. This is the custom type that you previously configured (see the note at the beginning of this section).

   3. In the Result set box, select or enter the variable to hold the result set.

   4. In the Max results box, specify how many results the procedure should return.

5. Configure Errors and Timeouts (see figure):
   
   

   1. Click the Errors and Timeouts tab.

   2. In the Timeout Duration text box, enter the amount of time that the process should try to do the database action before giving up.

   3. Select the radio buttons for the desired timeout and error handling methods.

6. If desired, click the Notes tab and add notes about the database action.

7. Click Close.