You can execute a stored procedure that you have defined previously and stored, and use it as a new SQL Statement.
To use a stored procedure:
1. From the Configuration tab of the DBAdapter Properties dialog box, click Configure to open the DBAdapter Configurator dialog box.
It displays the SQL Statements along with their types. You can add a new statement, or modify or delete selected statements. You can click Move Up and Move Down to change the order of the statements.
2. Click Add to open the SQL Statement dialog box.
3. Connect to a database and click the SQL Statement tab, which displays the available SQL statements.
4. Click Stored Procedure as the type, then click Choose Procedure to open the Stored Procedures dialog box, which displays the list of available stored procedures
5. From the list of procedures in the Stored Procedures dialog box, select a procedure and click Procedure Info to display the details about the selected procedure.
6. In the DBAdapter Configurator dialog box, click Variables to open the Variable Definitions dialog box. Once a stored procedure is selected; the input, output and I/O variables for that procedure get created and added to the existing list of variables. The input and output parameters of the stored procedure also get mapped to the corresponding variables.
You can add, modify or remove variables. If you change any variable, you must change the corresponding SQL statement appropriately.
7. From the SQL Statements dialog box, use the Input Parameters tab to view the defined input parameters.
8. Click the Output Parameters tab to view the defined output parameters.
Note: Result of a stored procedure execution is always a single row. Hence, by default, Expect Single Row option is selected, and all other options are disabled.
9. Click OK to return to the DBAdapter Configurator dialog box. Click OK to return to the Properties view.
10. Click Change Mapping.
For input parameters, only Source mapping can be defined. For output and return parameters only Target mapping can be defined. For I/O parameters, both Source as well as Target mapping can be defined. Note that this dialog box shows variables for all the stored procedures configured for a specific workstep.
