Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: Can I fill a temporary table with a stored procedure result set?

    In SQL Server I can fill a temporary table with the data return from a stored procedure.
    If I have the stored procedure sp_x like that

    create procedure sp_x
    as
    select a, b from c

    Then I create a temporary table temp with two columns similar to those returned by sp_x.

    Then I use or
    insert into temp
    exec sp_x

    And the temporary table will receive the data that the sp_x return.
    Can you tell me if it is possible to do that with DB2?
    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow No

    I don't think so

    you will have to specify the insert as
    part of the stored procedure

    a: begin

    yoursqlinsertstatement

    end a

    Please chech the PL reference

    HTH
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    Dec 2003
    Posts
    78
    if i did not misunderstand, it is possible.first piece of code below -EXAMPLECALLER- is a stored procedure that call the second stored procedure -EXAMPLE2, takes the result set of EXAMPLE2, creates a temp table, and fills the temporary table with the results, then returns the results to the screen as it selects and creates a result set from the temp table(containig result set of EXAMPLE2)


    *******SQL stored procedure (db2)***********
    CREATE PROCEDURE DB2ADMIN.EXAMPLECALLER (out var2 varchar(15),
    out var3 varchar(15),out var4 int)
    SPECIFIC DB2ADMIN.EXAMPLECALLER
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure

    --It works
    -- it calls a stored proc in the same database and return the result set
    ------------------------------------------------------------------------
    P1: BEGIN

    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;

    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPSALES
    (COLUMN2 VARCHAR(15),COLUMN3 VARCHAR(15),COLUMN4 int)
    WITH REPLACE
    ON COMMIT
    PRESERVE ROWS
    NOT LOGGED
    in BIG_TABLESPACE ;

    BEGIN
    DECLARE result1 RESULT_SET_LOCATOR VARYING;
    DECLARE cursor2 CURSOR WITH RETURN TO CALLER FOR
    SELECT * FROM SESSION.TEMPSALES;

    CALL EXAMPLE2();
    ASSOCIATE RESULT SET LOCATOR(result1) WITH PROCEDURE EXAMPLE2;
    ALLOCATE rsCur CURSOR FOR RESULT SET result1;

    WHILE (at_end = 0) DO
    FETCH FROM rsCur INTO var2,var3,var4;
    INSERT into SESSION.TEMPSALES values(var2,var3,var4) ;
    END WHILE;

    OPEN cursor2;

    END;



    END P1

    ***************END OF Stored procedure********************


    ************EXAMPLE2 stored procedure***************
    CREATE PROCEDURE DB2ADMIN.EXAMPLE6 ( )
    SPECIFIC DB2ADMIN.EXAMPLE6
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure that is called
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare cursor

    DECLARE cursor2 CURSOR WITH RETURN TO CALLER FOR
    SELECT
    DB2ADMIN.SALES.SALES_PERSON AS SALES_PERSON,
    DB2ADMIN.SALES.REGION AS REGION,
    DB2ADMIN.SALES.SALES AS SALES
    FROM
    DB2ADMIN.SALES;

    -- Cursor left open for client application
    OPEN cursor2;


    END P1

    ***********end of EXAMPLE2******************

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •