Stored Procedures in Database Mappings

In response to user requests, the new release of MapForce 2013 includes robust support for stored procedures in database mappings as:

• input components (for stored procedures that provide results)
• output components (for stored procedures that insert or update data in a database)
• or, stored procedures may be inserted as a function-like call (allowing users to provide input data, execute the stored procedure, and read/map the output data to other components)

The screenshot below shows the mapping of a stored procedure in SQL Server to create an XML file.

Stored Procedures are programs that are hosted and run on a database server, callable from a client application. These programs are normally written in SQL but some databases can also handle calls to routines in languages like Java, Visual Basic , or other code. Stored procedures can perform simple operations like retrieving data, inserting new data, or updating database tables, or stored procedures can perform complex database administration tasks. Support for stored procedures varies among database vendors.

Database architects often use stored procedures to implement business logic in the database, which can simplify data management, especially when requirements change over time.

Stored procedures often require input parameters. MapForce allows you to provide values for all stored procedure input parameters that are required when the mapping is executed.

The procedure at the center of the database mapping shown above returns a table of data showing all the managers in the chain of command above the specified employee ID, which is supplied as an input parameter. The employee ID in this example is defined in the mapping as a constant with the value 67. You can also supply the parameter in another form, such as a calculated value or a data element retrieved from elsewhere in the database.

One issue with mapping stored procedures is the required data structure is not visible in the mapping until the first time the procedure is executed. MapForce provides a context menu that lets you run the stored procedure to reveal the data structure and individual data types for mapping:

The Output component of the mapping shown above is an XML document. Executing the mapping generates this result:

The MapForce integrated Help and example files demonstrate various applications of stored procedures in database mappings.

Find out for yourself how easy it is to include stored procedures in your own database mapping projects! Download a free 30-day trial of MapForce 2013.