Welcome toVigges Developer Community-Open, Learning,Share
Welcome To Ask or Share your Answers For Others


0 votes
in Technique[技术] by (71.8m points)

sql - Query that returns multiple result sets

I have a query that will return an arbitrary number of result sets, all with the same columns - i.e. one for a Manager and then one each employee said Manager is responsible for.

Running the SQL in Mgmt Studio works fine - my 'master' and 'details' tables are successfully rendered. However if I simply enter the same SQL in a dataset query in SSRS Report Wizard, only the first result set gets reported on.

How can I get my one Master + all Details result sets rendered as separate tables in the one report? The key thing is that the query will generate an arbitrary number of result sets.

See Question&Answers more detail:os

Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Sadly, from the 2008 documentation:

...A result set from a relational database, which can result from running database commands, stored procedures, or user-defined functions. If multiple result sets are retrieved through a single query, only the first result set is processed, and all other result sets are ignored.

So I'm afraid you're limited to more convoluted options. I'd suggest either:

  • Returning all your result sets UNION ALLed into one large result set, with an extra column to indicate which manager/employee each section of the results is for, or
  • Creating a "master" report and accompanying code that displays a subreport for each of the managers/employees you're reporting on. The subreport would use a version of your stored procedure which renders your report results for one person at a time.

The latter will probably be the cleaner approach.

Welcome to Vigges Developer Community for programmer and developer-Open, Learning and Share