Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Posts
    5

    Angry Unanswered: db2 stored procedure

    Hi

    I'm trying to create an SQL stored procedure (calling sp) that calls an existing stored procedure (target sp) that produces a result set. Then using the result set from the target sp to define what the calling sp returns, by linking it into the where clause of the calling sp.

    This is similar to using a temporary table in SQL, by retrieving one result set that is held in the temp table, then using the result set to help define the data to be returned from the main select.

    I have looked at numerous documentation, but have not had much luck so far.

    Can anybody help please.

    Regards

    Shaun

  2. #2
    Join Date
    Jun 2003
    Posts
    5
    Below is the syntax for the target sp. This sp returns two records

    CREATE PROCEDURE SP.CALLER_SET (out fund_code char(5))
    RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE fundcur CURSOR WITH RETURN TO CALLER FOR
    SELECT F.FUND_CODE FROM FST.FUND F
    WHERE F.XD_DATE = '16.12.2003';
    OPEN fundcur;
    END P1

    Below is the sql for the calling sp.

    Select a.unitholder_id
    From FST.Account a
    Where a.fund_code = {result set from target sp}

    Not sure how to code this part.

    Regards

    Shaun

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by shaunz
    Select a.unitholder_id
    From FST.Account a
    Where a.fund_code = {result set from target sp}


    How about "WHERE A.FUND_CODE IN ..."?

  4. #4
    Join Date
    Jun 2003
    Posts
    5
    The issue isn't with the syntax of the sql, but how to get the second sp to recognise the result set from the first sp in the second sp's sql.

    Select a.unitholder_id
    From FST.Account a
    Where a.fund_code in fund_code

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by shaunz
    The issue isn't with the syntax of the sql, but how to get the second sp to recognise the result set from the first sp in the second sp's sql.

    Select a.unitholder_id
    From FST.Account a
    Where a.fund_code in fund_code
    I just realized what you're trying to accomplish. I don't think it will work with a nested SP since it returns a cursor, and you have to work it as with any other cursor in the calling procedure, that is using FETCH.

    May be you should use table function instead of the nested SP, which you can then refer to in "..WHERE A.FUND_CODE IN (SELECT FUND_CODE FROM TABLE(CALLER_SET()))"

    Nick

  6. #6
    Join Date
    Jun 2003
    Posts
    5
    Thanks Nick for your response.
    Basically what I'm doing is testing these stored procedures to see what they can do.

    ie. return a result set from a sp, and use that result set when calling another stored procedure.
    I know this could possibly be done by creating a physical table, and the sp populate the table.
    This is for the purpose of several queries (with slight differences) using the same result set which would return quickly, instead of several querys running the same query which would return slowly.

    I hope this makes sense.

    Shaun

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by shaunz

    ie. return a result set from a sp, and use that result set when calling another stored procedure.
    As I said, a nested SP can only return cursor, not a "result set". You can't use cursor in place of a table or subselect. You can only FETCH data from the cursor record by record.

    On the other hand, a table function returns something that looks like a table ("result set"); therefore, you can use such a function wherever you would use a subselect or a table name (including IN predicate)

    Hope this helps.

    Nick

  8. #8
    Join Date
    Jun 2003
    Posts
    5
    Can you give any advice using a table function?

    eg. Syntax etc.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by shaunz
    Can you give any advice using a table function?

    eg. Syntax etc.
    Table function is created as any other DB2 UDF. The only difference is in the "retrurns" clause:

    create function myfunc()
    returns table (<column definitions>)
    ...
    return
    select <columns> from ...

    You'll find more detail and examples in the DB2 SQL reference.

    http://www-3.ibm.com/cgi-bin/db2www/...bs.d2w/en_main

Posting Permissions

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