Adding Stored Procedures to the Mapping
On the mapping area, stored procedures are shown as part of the database component where they belong. In order for stored procedures to be visible on the database component, you must explicitly select them when adding the database component to the mapping, as shown below. In this example, we connect to the "AdventureWorks" database running on SQL Server. Instructions are similar for other database types.
In case of Oracle databases, stored procedures or functions may be standalone or part of Oracle packages. You can add both categories to the mapping. The stored procedures or functions belonging to a package appear under the respective package name on the "Insert Database Objects" dialog box illustrated below.
To add stored procedures to the mapping:
1.Do one of the following:
•On the Insert menu, click Database.
•Click the Insert Database ( 
) toolbar button.
2.Follow the database wizard steps until you get to the "Insert Database Objects" dialog box. For detailed instructions applicable to each database type, see Database Connection Examples.
3.Select the check boxes next to the database objects that you need to be displayed on the mapping, and click OK. In this example, we have selected all the tables, views, and stored procedures available in the "HumanResources" schema.
Notes •You can change the selected objects at any time later, by right-clicking the title bar of a database component, and selecting Add/Remove/Edit Database Objects from the context menu. •Your database user account must have rights to view and execute stored procedures in the database. |
The database component is now added to the mapping. Notice that stored procedures are identified by the 
icon. In addition, tables, views and procedures are sorted alphabetically in the database component.
The Show Context Menu 
button next to each stored procedure lets you configure how the stored procedure is to be called, and other procedure-related settings, as follows:
Option | Usage |
Show Nodes as Source | Select this option if you want to call a stored procedure without parameters in order to retrieve data from a database and map it to another component supported by MapForce (XML, text, EDI, and so on). For an example, see Stored Procedures as Data Source. |
Show Nodes as Target | Select this option if you want to call a stored procedure in order to modify the database or perform another specific action where you don't need the output of the stored procedure. For an example, see Stored Procedures in Target Components. |
Insert Call with Parameters | Select this option if you want to call a stored procedure with parameters and want to map the returned data to another component supported by MapForce. For an example, see Stored Procedures with Input and Output Parameters. |
Edit Recordset Structures | Applicable for stored procedures that return recordsets. Select this option to execute the stored procedure once, so that MapForce can determine the structure of the returned recordset and display it on the mapping. Alternatively, if you don't want to execute the stored procedure at design time, you can define the recordset structure manually. |
Procedure Settings | Applicable only for stored procedures that were configured as "target" (that is, those that update the database). Select this option to configure additional procedure-related settings, such as running a custom SQL query before calling the procedure, or enabling database transactions. |