Like SELECT [temp table1] = [subselect 1], [temp table2] = [subselect 2] FROM [Stored procedure]

Question

I have a stored procedure that returns the two choices that I use in the report. The first choice is the data to display in a tabular format, and the second is the metadata to display in the report header, as shown below:

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

Since sproc calculation is quite intense, I would like to prepare report lines as simple data (in two tables: PrecalcResults and PrecalcMetadata) for some of the most commonly used sproc parameters in one night. Rather, I would immediately select the pre-calculated values ​​or calculate them using sproc according to the parameters.

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

If I had a single select sproc, I would use this approach: Insert the results of the stored procedure into a temporary table

Since I have a multiselect sproc, I would like to do something like the above, but with two tables. In .net, I would do DataSet.Tables [0] and DataSet.Tables [1] ... but I want to do this in tsql to run it in everyday work.

Is this possible in MS SQL?

---

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

Answer 1

, .

, .

, TableAdapter , , , .