I'm looking to iterate over a result set in a stored procedure multiple
times. Ideally I'd like to use something like a cursor that doesn't need to
re-fetch the results, or being able to store the results in an in-memory
table or array. Is that possible without hitting the disk multiple times?

The background is that I have a multi-level category tree implemented as a
self-referencing table (ID, PARENTID, DESCRIPTION, many other columns).
There can be any number of levels (typically 4 or less). To date I've been
using a recursive stored procedure to re-create the tree in order but that
becomes quite slow once there are a few thousand categories and I now have a
case where there are 60,000+ categories in 6 levels. It takes almost 10
minutes to return the tree in the correct order due to the large number of
SELECT statements executed and possibly the overhead in recursion. It's a
multi-user system so having even a couple of people doing this
simultaneously would grind the server to a halt.

I've looked at many ways to speed this up. For simple presentation and
selection for the user I now load the categories on demand. Part of this
involves pre-reading the entire structure of the tree (ID, PARENTID, a
couple of other columns) in a single SELECT statement and reconstructing the
hierarchy in memory. For 60,000+ categories that takes about a second to
query and build the tree. The actual category DESCRIPTION and other columns
are loaded as required. But I also need to construct the tree in reports
(which join the category tree to other detail data, total values etc) and
this method wouldn't work too well (I'd need to select all of the detail
data and pull it back to the client to perform the join in memory - even if
only reporting on a sub-category).

This led me to wanting to write a stored procedure which would execute a
single SELECT statement to retrieve the entire category list and iterate
over the rows many times, in memory, to return a subset of them on each
iteration (those with a given PARENTID) so that the entire result set is
eventually returned in the correct order.

I know that there are other possibilities (such as adding a LINEAGE column)
but these are often difficult to maintain.

Thanks for any help.

Regards,
Jarrod Hollingworth
Backslash