Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2008
    Posts
    23

    Pass a set of values into a udf?

    Hi,

    I have a recursive query that I'd like to parametrize. The query is kind of long so I'll just give a basic example.


    Code:
    with temp(c1, c2, c3) as (
    select a, b, c from table
    -- Need to set parameters here -- where a in (5, 6, 7, 8, 9)
    union all
    select c1, c2, c3 from temp
    where not terminator)
    select * from temp
    -- Can't do this - where c1 in (5, 6, 7, 8, 9)
    The reason I need to put the params in the base case of the sql is probably obvious. The table has millions of rows and if I don't parametrize at the base, its going to recurse the whole table and then try to apply the filters.

    So it looks like my option is to create a UDF or stored procedure and then take the parameter and apply it to a where in the base case of the recursion. I can easily do that if all I wanted to filter on was one value, but I want to do a "where in".

    So 2 questions:

    1 - Is there a way to pass a variable sized set of values into one argument of a table UDF or stored procedure?

    2 - Are there any alternative methods to filtering a recursive query?

    One way I'm thinking is to pass a delimited string to a stored procedure. Then parse the string and pass the values into a temp table. Then in the procedure or udf, do a "where in" on the temp table. Outside of that, I'm stumped. Any ideas are appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,429
    Not sure that I fully understand your problem but you could try using the ARRAY data type introduced in DB2 9.5 - you can pass ARRAYs to SQL stored procedures, according to the documentation.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2008
    Posts
    23
    Yeah - I've found info on using arrays. Problem is our DB isn't 9.5.

    I haven't found out whether or not I can use an array within sql. In other words, could the SQL have something like

    "where key in myarray"

    The problem is that in order to filter recursive SQL, as far as I can tell, if you want it to perform the filter has to be in the base case (before the union all). Since we don't want to give our user the actual sql, we have to give them a table udf or a stored procedure in order to allow the injection of a filter into the base case (again, as far as I know).

    Thanks,
    Joe

Posting Permissions

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