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?

Thanks!

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.