How to SELECT [temp table1] = [subselect 1], [temp table2] = [subselect 2] FROM [Stored Procedure]

Question

I have a stored procedure that returns two selects, which I use in a report. The first select is data to display in tabular format and the second are metadata to display in the report head, like showed below:

CREATE PROCEDURE dbo. GetReport    
@Input INT
AS 
BEGIN          
   --Get #Metadata    
   -- #Results = f(#Metadata) … compex calculation          
   SELECT * FROM #Results    
   SELECT * FROM #Metadata    
END

As the sproc calculation is quite intensive, I would like to prepare the report lines as plain data (in two tables: PrecalcResults and PrecalcMetadata) for some mostly used sproc parameters overnight. Lather I would directly select the precalculated vaues or calculate them with the sproc according to the parameters.

For maintenance reasons I would like to use the same sproc to calculate data that would be: 1. showed in the report 2. be stored in PrecalcResults and PrecalcMetadata (with the used parameters)

If I would have single select sproc I would an approach desctibed here: Insert results of a stored procedure into a temporary table

As I have multiselect sproc I would like to do something like above but with two tables. In .net I would do DataSet.Tables[0] and DataSet.Tables[1]..., but I want to do it in tsql, to run it in daily job.

Is this even possible in MS SQL?

---

I have to apologize myself, from the answer below I can see I was not very clear. I would like to do implement this functionality as pure TSQL.

Answer 1

Yes, this is possible.

It's perfectly fine to return multiple result sets from a single stored procedure as you have suggested.

Your only potential issue is the limitation of a TableAdapter being able to pull both result sets from the stored procedure, but there's a very simple work-around for that issue.