Hi, in some parts of my application I call a stored procedure and this procedure insert rows in a temporary table, then I read this temp table from dephi to display or use the result.

I need to know if there is a way to select directly from the stored procedure, like : select * from my_procedure(param1, param2)...

There are similars solutions in sqlserver with Table-valued functions
and oracle with pipelined functions

thanks.

You could write the SP in such a way that it returns a result set, then you can associate a result set locator with the stored procedure (after the call) and fetch from that.

Table UDFs are also available in DB2.

This udf table function can return row by row, like oracle´s pipe row comamand ?

A table UDF returns a whole table. You implement it like an iterator where DB2 issues multiple FETCH operations against it to retrieve the next row each time. You can also use different input parameters for the table UDF in the same query. Each set of input parameters causes a new table to be produced and the rows in each of those tables is union-all'd with the tables of the preceding invocations.

p.s: I don't know Oracle's "pipe row command".

The main problem is the way I will build the rows in this table.
My knowledge in db2 language is very limited, but I´m thinking that the db2 way is different from the others.
The case is:

function x ..
returns table (....)

begin

//inside the function there is a way to insert rows in the table?


end;


ps: sorry about oracle stuff..

Let's start the other way around: what exactly is the problem you try to solve?

Hi stolze, thanks for reply.

In some cases I write a Stored procedure that makes a lot of bussines logic to find a list of records, lets say: a list of all people that has some disease. This record returns me the name, sex, birth date.. like:
John, M, 1970-01-10
Peter, M, 1945-10-23
..
Today I´m inserting this values in a table, and them I read it when its necessary.

If my SP returns me that result set I don´t need this temporary table.
Remember that I can´t resolve my all my SP using a single select statement.

Thank you

For your purposes, you can use a table function without any problems. The function has to select all rows that may have to be returned. Then you run a query like "SELECT ... FROM TABLE ( table-udf(...) ) AS t". If your table UDF is implemented as LANGUAGE SQL, it will be compiled into the query like a view.

If you need different result schemas, then you'll have to use a stored procedure and work on the result set. The reason is that SELECT statements all have a well-defined schema and you cannot simply add/remove columns for intermediate results or the final result of the query.

p.s: You can't use stored procedures in a SELECT statement. In standard SQL (and DB2 follows that), a procedure has to be executed using the CALL statement.

Digging the web I found an example that does the folowing:

1. creates a table function
2. inside the table function it declares a temporary table (on commit delete rows with replace not logged) exactly the same as table function table.
3. use this temp table to store the rows.
4. in the end he returns a select in this temp table.

this will work for me, if it works at all..

thanks.

Just a word of caution: depending on how the temp table is filled, it may be completely unnecessary to have a temp table in the first place. Typically, you don't need temp tables to hold/store intermediate results because each query returns a table, and if you can formulate a query that does what you need by the table function, you may be better of.

The drawback of creating an explicit temp table are pretty obvious: besides the overhead of materializing the result, DB2 may not be able to optimize the overall SQL statement since you introduced a barrier. You may get a sub-optimal query plan when using such a function.

Of course, all this depends on what you want to do and what you requirements are performance-wise and from a maintenance perspective.