Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2008
    Posts
    8

    Unanswered: Selectable stored procedures

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2008
    Posts
    8
    This udf table function can return row by row, like oracle´s pipe row comamand ?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Nov 2008
    Posts
    8
    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..

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Let's start the other way around: what exactly is the problem you try to solve?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Nov 2008
    Posts
    8
    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

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Nov 2008
    Posts
    8
    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.

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    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
  •