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.

Prerequisites

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

Steps to create Stored Procedure activity:

1. Click Configure > OTHERS > Stored Procedure.

2. Click Create Stored Procedure.
   
   
   
   

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

4. Select the database info activity from the Database Info drop-down list .

   Click here to learn how to create Database Info activity.

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

   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.

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

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

   
   

8. Expand the Advanced Properties section and select the Project from Project drop-down list.
   
9. Click Save.