Stored Procedures and SSRS 2008

I have a stored procedure in SQL开发者_如何学编程 Server 2008 that consists of multple select statements. When building a report in SSRS, I have a dataset that uses that stored procedure. However, the only fields that show up are the ones that are in the first select statement. Is there a way to show the other fields or use multiple select statements within one stored procedure?

Thanks!

Do the selects output the same schema (i.e. fields)? If so, and if you need all the results, you could try UNIONing the separate queries together. Otherwise, why not move the query you need into a new stored proc and call that from the report?

Eric, it's been my experience that when SSRS is based on a stored procdure, the results of the last Select statement are used and not the first. So you should be able to do anything you want up until the last Select statement in the stored proc and then make sure the last Select contains the correct data/columns for the report.

From the MSDN documentation

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