Getting Started

10.2
- 10.5.8
- 10.5

Back Next

Creating a Stored Procedure Transformation

When you install the sample database objects to create the source tables, you also create a stored procedure, SP_GET_ITEM_COUNT. This procedure takes one argument, an ITEM_ID value, and returns the number of times that item has been ordered.

The following table shows the syntax for the stored procedure:

Database	Syntax
Oracle	CREATE FUNCTION SP_GET_ITEM_COUNT (ARG_ITEM_ID IN NUMBER) RETURN NUMBER IS SP_RESULT NUMBER; BEGIN SELECT COUNT(*) INTO SP_RESULT FROM ORDER_ITEMS WHERE ITEM_ID = ARG_ITEM_ID; RETURN (SP_RESULT); END;
Microsoft SQL Server	CREATE PROCEDURE SP_GET_ITEM_COUNT (@ITEM_ID INT) AS SELECT COUNT(*) FROM ORDER_ITEMS WHERE ITEM_ID = @ITEM_ID
Sybase ASE	CREATE PROCEDURE SP_GET_ITEM_COUNT (@ITEM_ID INT) AS SELECT COUNT(*) FROM ORDER_ITEMS WHERE ITEM_ID = @ITEM_ID
Informix	CREATE PROCEDURE SP_GET_ITEM_COUNT (ITEM_ID_INPUT INT) RETURNING INT; DEFINE CNT INT; SELECT COUNT(*) INTO CNT FROM ORDER_ITEMS WHERE ITEM_ID = ITEM_ID_INPUT; RETURN CNT;
DB2	CREATE PROCEDURE SP_GET_ITEM_COUNT (IN ARG_ITEM_ID INT, OUT SP_RESULT INT, OUT SQLCODE_OUT INT ) LANGUAGE SQL P1: BEGIN -- Declare variables DECLARE SQLCODE INT DEFAULT 0; -- Declare handler DECLARE EXIT HANDLER FOR SQLEXCEPTION SET SQLCODE_OUT = SQLCODE; SELECT COUNT(*) INTO SP_RESULT FROM ORDER_ITEMS WHERE ITEM_ID=ARG_ITEM_ID; SET SQLCODE_OUT = SQLCODE; END P1
Teradata	CREATE PROCEDURE SP_GET_ITEM_COUNT (IN ARG_ITEM_ID integer, OUT SP_RESULT integer) BEGIN SELECT COUNT(*) INTO: SP_RESULT FROM ORDER_ITEMS WHERE ITEM_ID =: ARG_ITEM_ID; END;

In the mapping, add a Stored Procedure transformation to call this procedure. The Stored Procedure transformation returns the number of orders containing an item to an output port.

Create a Stored Procedure transformation and name it SP_GET_ITEM_COUNT.

The

Import Stored Procedure

dialog box appears.

Select the ODBC connection for the source database. Enter a user name, owner name, and password. Click

Connect

Select the stored procedure named SP_GET_ITEM_COUNT from the list and click

In the

Create Transformation

dialog box, click

Done

The Stored Procedure transformation appears in the mapping.

Open the Stored Procedure transformation, and click the

Properties

tab.

Click

Open

in the Connection Information section.

The

Select Database

dialog box appears.

Select the source database and click

You can call stored procedures in both source and target databases.

You can also select the built-in database connection variable,

$Source

. When you use

$Source

$Target

, the Integration Service determines which source database connection to use when it runs the session. If it cannot determine which connection to use, it fails the session.