Page Comparison

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 the layout of a stored procedure 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 the form of an XML stream. Ensure that you 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.

Follow the steps below to create 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.

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

   
   

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

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

   
   

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

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

   The variables are automatically populated in the 

   Info
   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.

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

9. available on the list.

   
   

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

    Info
    The output mode defines whether the stored procedure generates a stream or not. The stored procedure generates an XML output that contains Result Sets, Out, and InOut parameters' values when XML Stream is selected as an output mode. Selecting Context Parameters as an output mode sets the value of Out and InOut variables in context.

    
    

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

    • In case of the Context Parameters as the output mode:

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

     fields, these

    • 1. , in Stored Procedure Parameters section.
         

         Tip
         These fields are editable though.

    The 

    • 1. The @ symbol precedes the variable name in case of MS SQL database, however, for Oracle database @ symbol is not required.

    Expand the Advanced Properties section and select the Project from Project drop-down list

    • 1. 
         

    • In case of the XML Stream as the output mode:

      1. The Define Layout section is visible wherein you can create the layout of the stored procedure by defining/adding the queries for the result set(s), the Out, and the InOut Parameters

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

13. Click Save.