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.

The page guides you on how to create a stored procedure service in Adeptia Connect to retrieve a single value or a result set(s) generated by a stored procedure. You will also come to know when and how you can define stored procedure layouts in sections to follow.  

Creating a stored procedure service

A Stored Procedure service in Adeptia Connect helps you retrieve the output generated by a stored procedure of a database. The output can be a single value retrieved in a context variable or multiple values retrieved as a result set in an XML stream. 

Ensure that you meet the following prerequisites before you start creating a Stored Procedure service.

Prerequisites

• Database Info activity must be created before creating Stored Procedure activity.
• Ensure that you have the stored procedure written in the database.

Follow the steps below to create a Stored Procedure service:

1. Click Configure > OTHERS > Stored Procedure.

2. Click Create Stored Procedure.
   
   
   

3. In the Name field, enter the name of the stored procedure service.
4. In the Description field, enter the description of the stored procedure service.

5. in the Database Info field, select the Database Info service.

   Click here to learn how to create a Database Info activity.

6. In the Select Schema field, select the schema.

   The stored procedures associated with this schema are populated in the next field.

   In case you are using an Oracle database, you will have one additional field, Package Name, wherein you can select the name of the Package if the stored procedure that you are going to select in the next step belongs to that package. 

7. In the Select Stored Procedure field, select the stored procedure from the list.
   

   Make sure that you have the execute permission on the stored procedures in the database as only the procedures for which the database user has permission are available on the list.

8. In the Stored Procedure Parameters section, in the Output Mode field, select either of the two modes Context Parameters and XML Stream. The default output mode is Context Parameters.
   

9. The subsequent steps to create the stored procedure service differ based on the output mode selected. 
   

   In the case of the Context Parameters, the Parameters of the selected stored procedure are automatically populated in the respective Parameter's fields, In Params, Out Params, and InOut Params, in the Stored Procedure Parameters section.

   The output mode defines whether the stored procedure generates a stream or not. The stored procedure generates an XML output that contains the Result Sets, the Out, and the InOut parameters' values when XML Stream is selected as the output mode. Selecting the Context Parameters as the output mode sets the value of the Out and InOut variables in context.
   These fields are editable though. The @ symbol precedes the variable name in the case of the MS SQL database, however, for the Oracle database @ symbol is not required.

   In the case of the XML Stream as the Output Mode:

   • The Parameters of the selected stored procedure are automatically populated in the respective Parameter's fields, In Params, Out Params, and InOut Params.
     

   • The Define Layout section is visible wherein you can define/add the queries for the result set(s), the Out, and the InOut Parameters to create the layout of the stored procedure.

     
     

   • The stored procedure layout can have the following input and output details:

     1. Result Set(s)

     2. Out Params

     3. IN Params

     4. Return value

     The Define Layout section is visible only when you have selected the XML stream as the Output Mode. The return value is applicable in the case of SQL Server only.

10. Follow the steps below to define the stored procedure layout

    1. In the Add Query section, Click Browse Tables.
       Browse Tables window opens.

       
       

    2. in the Select Schema Name field, select the name of the schema.

    3. In the Select Table field, select the table from which you want to have the fields in your query.
    4. In the Column Details section, select the fields individually or all of them by selecting the check box Column Name.

    5. Click Generate Query.
       The query for the result set gets generated and appears in the field Query in the Add Query section.

       You can add more queries to create multiple result sets by clicking . These result sets for the layout should be in the same sequence as that in the selected stored procedure of the database.

    6. Select the Type of Out Params.

    7. If you have selected Result Set as the Type, repeat the sub-steps from a through e (step 10).

    8. Select the Type of In Out Param.

    9. If you have selected Result Set as the Type, repeat the sub-steps from a through e (step 10).

       The type of output parameters here indicates whether they can hold a single value or multiple values. For both of the above parameters, you can select the type as Value or Result set. While the parameters with type Value can hold a single value as an output, the Result Set type of parameters can have multiple values retrieved (as a result set). 

       The Result set type of parameters is useful when you are dealing with an Oracle database where you cannot directly write/use a SELECT statement in a stored procedure to generate a result set.

11. In the Project field, select the project from the list.
    

12. Click Save.