How can I use query-of-query UNION on n-recordsets when var scoping is needed?

Question

I would like to be able to do a query of a query to UNION an unknown number of recordset. However when doing a query-of-query dots or brackets are not allowed in record set names.

For example this fails:

<cfquery name="allRecs" dbtype="query">
    SELECT * FROM recordset[1]
    UNION
    SELECT * FROM recordset[2]
</cfquery>

Using dynamic variable names such as "recordset1" work but this is in a function and needs to be var-scoped so I can't build up the variable names dynamically without producing memory leaks in a persisted object.

Any other ideas?

Answer 1

After posting the question I came up with a couple solutions but there might be a better one out there

• I could write dynamically named variables to the arguments scope and then reference them without their scope in query

• Create a function that accepts 2 recordsets as arguments and returns one combined recordset. This could be looped over to progressively add a recordset at a time. I'm sure this is very inefficient compared to doing all UNIONs in one query though.