How to use Multiple result sets in Reporting Services

I have a stored procedure which returns multiple result sets similiar to the following:

ALTER PROCEDURE sp_XXXX 
(
 XXXXXX
)
AS
SET NOCOUNT ON

SELECT XXXXXXX    


IF @@ROWCOUNT = 0
 SELECT     XXXXXXX



RETURN

I want my report to use the first result set if it has data or use the second one in case the first one is empty. Any help?

From stackoverflow

• In the sproc "union all" your two result sets. If you need to tell them apart add a derived column indicating the original result set.

  select 'ds1' as dataset, *
  from table1
  union all
  select 'ds2' as dataset, *
  from table2
  

  Another try

  Dump result set 1 into a temp table and only execute the second query if it's empty.

  pseudo code:

  select * into #tempResult 
  from table 1
  
  if table1 is empty 
  
  select * from table2
  

  Ganesha : Thanks for your reply. The problem with using union all is that I want the select statement to be run only if the first one returns no rows.

  jms : you can dump result set 1 into a temp table and only execute the second query if it's empty.

  Ganesha : Thanks. Yes I finally ended up creating a temporary Table variable in SP. Curious to know why reporting services OOB does not support multiple result sets. One more approach could be I can write a WebService and use XML data source and write the logic in WebService

  jms : Excellent. Glad it works. Not going to say SSRS can't do it haven't tried, but I would avoid it anyways. I'll jump on any chance not to include business logic in the presentation layer. :)