Creating Stored Procedure

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. Stored procedures can be compiled and executed with different parameters and they may have any combination of input, output, and input/output parameters.

Limitation

• Adeptia Server stored procedure activity is supported for MS SQL, Oracle and Sybase version 9.0.2.

Prerequisites

• Database Info activity must be created before creating Stored Procedure activity.

Using Adeptia Server's Stored Procedure activity, you can execute a database stored procedure. Stored Procedure activity takes IN parameters from process flow context. Therefore, you need to set the value of IN parameters in the process flow context. To do so, follow the steps below.
 

Steps to set the values of IN or INOUT parameters using put-context-var:

1. Click hierarchy structure in the Repository View panel. Expand the Action list and select the put-context-var action and drag it to the Graph Canvas area just before the stored procedure activity.

2. Connect the put-context-var action to this activity.

   

   Connect Put-Context-Var to activity

3. Right-click put-context-var and select View Properties.

   Its properties are displayed in the Properties Panel in the Bottom Pane.

   
   
   Properties of Put-Context-Var

4. Click the Edit button to edit the value of context variable. The Edit Context Variables dialog box is displayed.
5. Click Add Variable (  ) to add new context variable. The Context Variable Information screen is displayed.

6. Select the Stored Procedure activity from the Activity Name dropdown list.

   As you select store procedure activity name in the put-context-var, then two properties are displayed in the Variable Name dropdown list.

7. Select the IN or INOUT parameter from Variable Name dropdown list.

   After selecting a variable, you need to edit this variable by adding ".<<variableName>>".

   
   Context Variable Details for Defining Parameters

8. Give the value of the parameter in Variable Value text box.

   

   Context Variable Details for Defining Parameter Values

9. Click Done to close the Context Variable Information screen. This takes the control back to the Edit Context Variables screen. The newly created variable is added to the list of existing context variables.
10. Click Done to close Edit Context Variables screen and return to Process Designer.

11. Save the process flow and exit from Process Designer.

    You can also set this input variable using a Process flow Variable or a custom plugin.

     

    For Custom plugin and Process flow variable, if the selected stored procedure has IN parameters as empid, you have to set an input variable with the name Service.<ActivityName>.InParams.empid, where <Activity Name> is the name of the stored procedure activity, which is used in the process flow.
    The variable name for INOUT parameter will be Service.<ActivityName>.InOutParams.count, where <Activity Name> is the name of the activity, which is used to execute the process flow and count is the INOUT parameter of the stored procedure.
    Similarly, OUT parameters of the stored procedure are set to process flow context. For example, if the selected stored procedure has OUT parameters as salary, the output variable is set in the process flow context as Service.<ActivityName>.OutParams.salary, where <Activity Name> is the name of the activity, which is used to execute the process flow. You can further use this variable and its value is the process flow. Value of output variable set by stored procedure activity is always in string format. You need to type cast it in a required format. The stored procedure doesn't generate a stream. Therefore, you have to use context source activity after stored procedure activity. To know how to create a context source, refer to the section Using Context Source and Context Target.
    
    
    
    Steps to create Stored Procedure activity

1. On the Adeptia Suite homepage, click the Develop tab.

2. Go to Services > Miscellaneous and then click Stored Procedure.

   The Manage Stored Procedure screen is displayed (see Figure 617).

   

   Manage Stored Procedure

3. Click the Create New link. The Create Stored Procedure screen is displayed.

   

   Create Stored Procedure

4. Enter the name and description for new stored procedure activity in the textbox Name and Description respectively.

5. Select the database info activity from the dropdown list Database Info Id.

   To learn how to create Database Info activity, refer to the section ...Creating Database Info of the Administrator Guide.

6. To select the database stored procedure, click the Browse button. The Select Stored Procedure screen with the list of stored procedures is displayed.

   

   Select Stored Procedure

   Make sure that you have execute permissions on the database. As, only the procedures for which the database user has permission are shown on the Browse screen.

7. Select the required stored procedure and click the Get Parameters button. The Stored Procedure Parameter screen is displayed with list of parameters.

   

    View Parameters

8. Click OK to return to the Stored Procedure activity screen. Parameters of the selected procedure are populated in Stored Procedure Parameters field.

   
   Manage Stored Procedure
    

   The variables are automatically populated in the In Params, Out Params, and InOut Params fields, these fields are editable though. The @ symbol precedes the variable name in case of MS SQL database, however, for Oracle database @ symbol is not required.

   
   

9. In the Stored Procedure activity screen click the Save button.