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?

jason saldo · Accepted Answer · 2009-02-19 21:46:15Z

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

edited Feb 19, 2009 at 21:46

answered Feb 19, 2009 at 21:25

jason saldo

10k5 gold badges36 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ganesha Over a year ago

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.

jason saldo Over a year ago

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

Ganesha Over a year ago

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

jason saldo Over a year ago

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. :)

Collectives™ on Stack Overflow

How to use Multiple result sets in Reporting Services

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related