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.
dBforums
>
Pass a set of values into a udf?
10-09-08, 11:59
Linear Mode
