How to use Multiple result sets in Reporting Services

Question

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?

Answer 1

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