Union two or more tables, when you don't know the number of tables you are merging

Question

I am working with MS SQL 2005. I have defined a tree structure as:

1
|\
2 3
   /|\
4 5 6

I have made a SQL-function Subs(id), that gets the id, and returns the subtree table. So, Subs(3) will return 4 rows with 3,4,5,6, while Subs(2) will return one row, with 2.

I have a select statement that returns the above Ids (joining this tree with other tables)

I want after the select statement that returns the above Ids
(which will result in a table with for example 2 rows:
2
3
)
to be able to run the Subs-fuction as
Subs(2)
union
Subs(3).
(The result should be the rows with id 2,3,4,5,6)

The problem is that I don't know how to pass the arguments and I don't know how to make the dynamic use of union.

Is it possible to solve this at this level, or should I take it to the upper-level (C#)?

Answer 1

I think you want to read up on Recursive Common Table Expressions.

Answer 2

I do not think you need UNION here, with SQL Server 2005 you can achieve the desired result using CROSS APPLY:

select 
    f.* 
from 
    resultsTable rt
    cross apply dbo.subs(rt.ID) f

That is assuming that the resultTable is the one that stores the results of your first query, and the name of the field is ID