5

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.

Community
  • 1
  • 1
Cerkvenic
  • 277
  • 2
  • 3
  • 9
  • Try searching for "multiple active recordsets" (aka MARS). You'll need to use the `NextRecordset` method to move from one recordset to the next. – HABO Apr 12 '12 at 19:46

1 Answers1

2

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.

Community
  • 1
  • 1
Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
  • unless it is a temp table, then it will not be found because it only exists on the server – Taryn Apr 12 '12 at 19:54
  • @bluefeet That hasn't been my experience... that could be an issue with your data access layer, or attempting to reference the temp table on a separate connection... It should be invisible to the client whether or not the source for a select statement is a temporary or non-temporary table. – Michael Fredrickson Apr 12 '12 at 19:58
  • Well it depends on how you are using it, if you have a temp table returning data in a stored proc and you add the stored procedure to a DataSet.xsd file. My experience is that it is not aware of the temp table at that point since it exists on the server only – Taryn Apr 12 '12 at 20:07
  • @bluefeet Ah... that's because Visual Studio is taking a short-cut and trying to determine the metadata of your stored procedure without actually executing it, which is where the temporary table causes an issue. If you were to add [`SET FMTONLY OFF`](http://msdn.microsoft.com/en-us/library/ms173839.aspx) to the top of your stored procedure containing the temporary table, it would work as expected. [Please see this discussion regarding the issue.](http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/2e8b86eb-9c3d-439f-a904-b457ad9c0a2c/) – Michael Fredrickson Apr 12 '12 at 21:49
  • wow that is awesome, I was not aware of that. I will need to keep it in mind for future projects. – Taryn Apr 12 '12 at 21:55