Stored Procedures with Input and Output
This example shows you how to call a procedure that takes input parameters and also retrieves some output from the database. In this scenario, the stored procedure is called similar to a Web service, or a function, and you can map data retrieved by it to any other target component supported by MapForce.
Let us first create the demo stored procedure in the "AdventureWorks" database. To do this, run the script below against the database. You can do this from a query window of Microsoft SQL Server Management Studio, or directly from the DB Query pane of MapForce, see Browsing and Querying Databases. In either case, make sure that your database user account has permission to create stored procedures.
CREATE PROCEDURE Production.uspSearchProducts |
The stored procedure above retrieves a recordset containing product information. It takes as input two parameters: a string with the product name (@SearchString) and the maximum product price (@MaxPrice). In addition to the recordset and the default return parameter, it also retrieves an output parameter (@ComparePrice).
The following steps show you how to create a mapping that consumes data returned by this procedure.
1.Connect to the "AdventureWorks" database from MapForce and add the stored procedure to the mapping, as described in Adding Stored Procedures to the Mapping. Make sure that your database user account has permission to view and execute stored procedures.
2.Click the Show Context Menu 
button next to the stored procedure and select Insert Call with Parameters. The stored procedure now appears in a separate component on the mapping, where the left side lists the input parameters, and the right side contains the return and the output parameters.
3.Click the Show Context Menu button again, and select Edit Recordset Structures. This is necessary so as to provide to MapForce information about the structure of the recordset returned by the procedure. The "Recordset Structures" dialog box appears.
Calling a stored procedure at design time may have side effects (depending on the procedure implementation). If you do not want to execute the stored procedure at design time, do not click Execute, as further described in subsequent steps. Instead, define the expected recordset in the "Recordset Structures" dialog box, by adding recordsets and their associated columns manually. Use the Add recordset or Add column buttons in the "Recordset Structures" dialog box. |
4.Click Define input parameters and call procedure. The "Evaluate Stored Procedure" dialog box appears.
5.Fill in the parameter values as shown above, and click Execute.
6.Click OK. The recordset structure ("RS1") is now visible both on the "Recordset Structures" dialog box and on the mapping.
7.At this stage, you can add a target component where the retrieved data will be written. In this example, data will be written to a CSV file. On the Insert menu, click Text File, and add a CSV component to the mapping, see also CSV and Text Files. Next, draw the mapping connections as illustrated below. Notice that the procedure's input parameters are supplied by means of constants. For more information about constants, see Add a Constant to the Mapping.
You can now preview the mapping. Click the Output button and observe the mapping result in the Output pane, for example:
