Multiple Result Sets

Question

Hi,

 

I am aware that SSRS does not support stored procedures with multiple data sets, but what if the same stored procedure may only return one set based on a parameter?

 

For example, lets say if parameter @TableID = 0, the stored procedure returns 2 result sets.  But if @TableID = 1, or @TableID = 2, the SP only brings back 1 result set.  Is this also not supported?

 

When I try to do this it still only brings back the fields from the first table.  Am I doing something wrong?

 

Thanks for any insight.

Answer 1

I have still had no luck on this issue.  Is there some sort of caching issue that is causing the dataset fields to not refresh?

 

Answer 2

The dataset fields are generated at design time when you click the Refresh Fields button. You cannot have a dataset whose field definition changes between report runs. However, you can have a dataset that has a superset of all the columns returned by dataset 1 and dataset 2. To do so, get the fields for dataset 1 and then manually add the dataset 2 fields on the Data tab of the Report Designer. 

Answer 3

You can try to have the same field names and types for any output.

---

Alun Davies