Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009

    Question Unanswered: Stored procedure calling sp, filtering returned 'child' data back to calling entity

    Okay, so I'm on DB2 v8.x and I'm trying to have a stored procedure call a stored procedure. This isn't a problem in itself, but what I'd like to do is have the parent then return a filtered set of data from what was returned from the child sp. Need more explanation?

    I'm using .NET to call the parent DB2 sp.
    1) Parent sp calls child sp to pull some set of data. The BIG pull of everything
    2) Child returns big data set back to parent.
    3) Within parent determine which column is needed (from input param) and take only that needed column of child's BIG data and return that back to the calling entity with return.

    So I was thinking about a temporary table in the parent that will house the data. Problem is, you can't do this (simplified without CREATE TABLE stmt, etc):

    set tablename = call childsproc();

    How can I set the temporary table to house the child BIG pull? From here, I'd think I could loop through inserting records into another table, then use a cursor to select this data and return it. Get where I'm coming from?


  2. #2
    Join Date
    Jan 2007
    Jena, Germany
    First, I think it is not a good idea to get all the data and then do some post-processing (filtering) on it. Instead, you should try to pass the filtering predicates and projects right to the "child sp". That will be faster and easier, too.

    As for the actual question: you can declare a temporary table, associate a cursor with the result set returned by "child sp", fetch from the cursor and insert the fetched data into the temp table.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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