Table of Contents

Search

  1. Preface
  2. Product Overview
  3. Before You Begin
  4. Tutorial Lesson 1
  5. Tutorial Lesson 2
  6. Tutorial Lesson 3
  7. Tutorial Lesson 4
  8. Tutorial Lesson 5
  9. Tutorial Lesson 6
  10. Appendix A: Naming Conventions
  11. Appendix B: Glossary

Getting Started

Getting Started

Creating a Stored Procedure Transformation

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.
  1. Create a Stored Procedure transformation and name it SP_GET_ITEM_COUNT.
    The
    Import Stored Procedure
    dialog box appears.
  2. Select the ODBC connection for the source database. Enter a user name, owner name, and password. Click
    Connect
    .
  3. Select the stored procedure named SP_GET_ITEM_COUNT from the list and click
    OK
    .
  4. In the
    Create Transformation
    dialog box, click
    Done
    .
    The Stored Procedure transformation appears in the mapping.
  5. Open the Stored Procedure transformation, and click the
    Properties
    tab.
  6. Click
    Open
    in the Connection Information section.
    The
    Select Database
    dialog box appears.
  7. Select the source database and click
    OK
    .
    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
    or
    $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.
    The following image shows the
    Properties
    tab of Stored Procedure transformation SP_GET_ITEM_COUNT:
    On the Properties tab, TUTORIAL_SOURCE appears at the Connection Information value.
  8. Click
    OK
    .
  9. Connect the ITEM_ID column from the Source Qualifier transformation to the ITEM_ID column in the Stored Procedure transformation.
  10. Connect the RETURN_VALUE column from the Stored Procedure transformation to the NUMBER_ORDERED column in the target table F_PROMO_ITEMS.
  11. Click
    Repository
    Save
    .