如何在 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?

答

在 sproc "union all" 你的两个结果集.如果您需要区分它们，请添加一个指示原始结果集的派生列.

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

再试一次

将结果集 1 转储到临时表中，只有在它为空时才执行第二个查询.

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

伪代码:

select * into #tempResult 
from table 1

if table1 is empty 

select * from table2