Store result set in memory over multiple queries?

I thought CTEs were perfect for my stored procedure, until I found out they can only be referenced in ONE query (ie the query immediately following the CTE). They now seem 开发者_如何学编程fairly pointless.

I'm looking for a way to perform a query, store that result set in memory (not on disk like a temp table or table variable), and then reference it within a stored procedure in multiple SELECT/INSERT/UPDATE statements. Just like a temporary view, or a CTE with a longer scope. But that doesn't seem to exist at all in SQL Server! Does anyone have a solution? Doesn't this seem rather short-sighted on behalf of the CTE functionality?

I agree with the comments. I also don't know precisely how the following would be done, but I think it would work. With that said: have your query generate XML output, store that output in an XML variable, and then use xquery to reference and extract data from it for the rest of the procedure. (But again, I'm not at all sure you can write a "SELECT @XML = ..." query. Maybe OPENXML, or something like that?)