Stored Procedure Group

Stored Procedure Group

Watch the video

A stored procedure group will interact with a database stored procedure. A stored procedure in a database is like a function. The procedure has a name and it may or may not take parameters. Once the procedure is called and the parameters passed into the procedure, the query executes on the database server and a the resultset is returned. The actual text of the query is stored and managed on the database server. Stored procedure provide an increased level of security and maintenance that make them ideal for certain scenarios.

Parameters in the Stored Procedure Group

When using a Stored Procedure Group, parameters may be configured to each item based on the type of the parameter:

• The Target Name column is used for writing, so specifying an IN or INOUT parameters under this column will have the item try to write its value to the parameter
• The Output column is used to move the value of an OUT or INOUT parameter into an item in the group. Note that if an item in a group is configured to reference an OUT parameters, its Target Name value should be set to Read-Only

A general reference of how items should be configured based on the type of the associated parameter can be seen below.

This is a very simple scenario. Often stored procedures contain very long or complicated queries. Lets say you want to use a transaction group to call this stored procedure and then to handle the result by writing to a tag location.

To use a transaction group to call a stored procedure

1. Create a new Stored Procedure transaction group.
2. Drag two tags into the Basic OPC/Group Items section of the group. The two tags must be of the same datatype as the stored procedure's parameters. In this case the two tags will be a string and an integer type.
3. Go to the Action tab of the transaction group, select the appropriate Data source. Select the procedure name from the procedure name drop down menu.
4. In the Basic OPC/Group Items section, from the Target Name column dropdown of the string tag, select the input parameter, and select None for the output.
5. In the Basic OPC/Group Items section, from the Target Name column drop down of the integer tag, select Read-only, and select the output parameter for the output.
6. Click Enabled and save the project to start the transaction group.

note

When running a Stored Procedure from an Oracle database, you cannot use named parameters. Instead you must use the index (number) arguments. IE: 1 is the first argument, 2 is the second, etc.