Using a Stored Procedure that Returns Multiple Results

Microsoft did a brilliant thing in SQL Server.  I applaud them for this.  You can actually pack more than one SQL Query into a stored procedure and have a single procedure return multiple result sets in one pass.  So I used this feature to write a stored procedure that gathers all the data I need for a certain .rdlc report in my app.  Now I can use this one stored procedure to gather all the data in one fell swoop.

I have done this kind of multiple result set stored procedures in the past and it's a simple matter in code to sort out the data tables, as they are returned in order. Here is some sample code for another instance of this method.

/// <summary>
/// gets the dataset from the database.
/// </summary>
public void Load_Data()
{
    SqlCommand myCommand = new SqlCommand("[dbo].[ExtractWebData]", myConnection);
    myCommand.CommandType = CommandType.StoredProcedure;

    System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(myCommand);
    ds = new DataSet();
    adapter.Fill(ds);
    ds.Tables[0].TableName = "Customers";
    ds.Tables[1].TableName = "Purchase_Orders";
    ds.Tables[2].TableName = "Purchase_Order_Details";
    ds.Tables[3].TableName = "Inventory";           
}

Now if there was just some way to use that in a report.  It would be great to use a stored procedure to twist all the data into shape before handing it off to a reporting tool!  So you create an xsd. You add a table adapter.

You tell it to use your existing stored procedure.

 You select your stored procedure, and there's the first dataset... But wait.  How do you tell it where the other result sets are?  I want to use all 3 datasets! 



Except that you can't do that.  You see, the .rdlc report requires that you have the data available at design time in order to design the report.  And there is no way to import multiple datasets at once into the .xsd at design time.

From this document: http://msdn.microsoft.com/en-us/library/dd239331.aspx

If multiple result sets are retrieved through a single query, only the first result set is processed, and all other result sets are ignored. For example, when you run the following query in the text-based query designer, only the result set for Production.Product appears in the result pane:

SELECT ProductID FROM Production.Product
GO
SELECT ContactID FROM Person.Contact

I have no idea what the text-based query designer is, but as we saw in SQL Server Management Studio...

In my opinion, this is an EPIC DESIGN FAIL on the part of Microsoft.

We know from the earlier code snippet that Visual Studio can access the data, it just - for some stupid reason - is designed in such a way as to disable this feature in certain cases.  This is completely unacceptable.  But until Microsoft fixes this glaring, stupid, boneheaded omission, we're stuck with it.

Now, I know this blog has no regular readers.  You didn't find this page because you're a fan of Visual Studio Journey.  You found it because you were googling for this problem, and there were no answers anywhere else.  I wish I had better news, but I don't.

Here is the only way I know to work around this issue.  Unravel your stored procedure and execute the whole thing in your client.  Convert each piece into individual queries, and add those to your .xsd.

Alternately, you could split your stored procedure into multiple pieces, like Proc1, Proc2, Proc3... but then you've kind of lost the convenience of the one-stop shopping the stored procedure offers.

Just to be clear: I think that the ability to return multiple result sets from a stored procedure is awesome!  Kudos to the SQL Server development team.  If only the Visual Studio guys would catch some of that brilliance, things would be great.

Addendum:
Just to make myself clear, I think that this feature has awesome potential and multi-table stored procedures are still incredibly useful in Visual Studio.  They just are not usable for rdlc reports (the one thing they would be most ideally suited for in an ideal world).

...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.