blocks|key|648595|text|试试这样的东西：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|648596|Create+StoredProcedure+sp_MultipleDataSets(@Param+nvarchar(10))
as
begin
+++if(@Param+==+"first")
++++++begin
+++++++++SELECT+EMPID,+ENAME,+JOB,+SAL,+DEPTID+FROM+EMP+--+first+result+set
++++++end
+++if(@Param+==+"second")
++++++begin
++++++++SELECT+DEPTID,+DNAME,+LOC+FROM+DEPT+--second+result+set
++++++end
end|code-block|syntax|javascript|648597|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try something like that:

<pre><code>Create StoredProcedure sp_MultipleDataSets(@Param nvarchar(10))
as
begin
 if(@Param == "first")
 begin
 SELECT EMPID, ENAME, JOB, SAL, DEPTID FROM EMP -- first result set
 end
 if(@Param == "second")
 begin
 SELECT DEPTID, DNAME, LOC FROM DEPT --second result set
 end
end
</code></pre>

blocks|key|648713|text|我一直使用SP中的一个参数来拉取SSRS中的多个结果集。您必须通过SP中的IF语句将它们分开，然后您还必须手动键入SSRS数据集设置中的字段。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|648714|看起来怪怪的，但它很有效。|648715|下面是一个例子。|648716|存储过程(SP)定义了两个参数@OfficerID+@DatasetFlag|648717|@OfficerID是必须输入密码或输入的Employee+#+(SSRS数据输入表单)。DatasetFlag是我控制在SP中执行哪条IF语句的方式。|648718|下面是SP：|648719|CREATE+PROCEDURE+[dbo].[StoredProcedureNameHere]
++++@OfficerID++VARCHAR(7),
@DatasetFlag++++VARCHAR(60)+++

WITH+RECOMPILE
AS
SET+TRANSACTION+ISOLATION+LEVEL+READ+UNCOMMITTED+
SET+NOCOUNT+ON;

BEGIN+TRY
IF+@DatasetFlag+=+'EmployeeName'
++++Begin
++++++++SELECT+[EmployeeName]
++++++++FROM+[DatabaseName].[scema].[Employee]
++++++++where+EmployeeBNumber+=+@OfficerID
++++END
ELSE
IF+@DatasetFlag+=+'Expect'
++++Begin
++++++++SELECT+
++++++++++++[TerritoryName]
++++++++++++,[TestNumber]
++++++++++++,[RuleNumber]
++++++++++++,[Expectation]
++++++++++++,[TestCount]
++++++++++++,[PercentToGoal]
++++++++FROM++++[Database].[scema].[Table2]
++++++++WHERE+OfficerID+=+@OfficerID
++++++++ORDER+BY+TerritoryID
++++++++++++++++,TestNumber
++++++++++++++++,RuleNumber
++++END

RETURN
GO|code-block|syntax|javascript|648720|这个报告有两个数据集，一个是我创建的，它会拉入参数，另一个是我创建的EmployeeName，它会拉入EmployeeName，因为SSRS只能使用一个结果集~！但是...我骗了它..。|648721|但我只是简单地通过OVERTYPING+EMPLOYENAME创建字段，然后添加其余的(查询)，因此编辑数据集属性中的字段并输入Select列名。|648722|然后，我使用参数菜单(数据集属性)为第一个数据集输入="EmployeeName“表达式，为第二个数据集输入="Expect”表达式。我也匹配了那个屏幕上的@OfficeID。|648723|然后当我运行这个的时候。它要求输入OfficerID+(RS创建输入表单)，以及当我输入ID并点击查看报告时。或者我们可以使用OfficerID来处理所有的RDL，就像SSRS表单所做的那样，只不过是在一个ASPX页面中。|648724|两个数据集都会返回(我假设它会调用两次)|648725|因此，在UNION数据集上没有奇怪的过滤或其他技巧，我必须在SSRS中处理，这并不有趣……(说真的，IIF?)|648726|我见过一个存储过程，其中我们有大约20个参数，因此您可以在SQL级别过滤报告的输出，而不是在报告中进行巨大的提取和过滤。|648727|不，您可以简单地创建20个存储过程，是的，但是这样，所有代码都在一个位置，当然它可以使用selects+into+TEMP+table来合并成吨的内容，最后只需填充结果集的表即可。|648728|作为一名程序员，我发现SSRS有点古怪，但到目前为止，我很开心，试图找到获得我想要的东西的方法，而不是它提供的东西……|648729|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1B|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1C|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|1D|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1E|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1F|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|1G|8|@]|9|@]|A|$O|P]]|$1|Q|3|R|5|6|7|1H|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1I|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1J|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1K|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1L|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1M|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|1N|8|@]|9|@]|A|$]]|$1|14|3|15|5|6|7|1O|8|@]|9|@]|A|$]]|$1|16|3|17|5|6|7|1P|8|@]|9|@]|A|$]]|$1|18|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]]|19|$]]

I use a Parameter in the SP to pull multiple Result sets in SSRS all the time.
You have to separate them by IF statements in the SP and also you then have to HAND TYPE out the FIELDS in the SSRS Dataset setup.

Seems whacky, but it works...

Here is a example.

Stored Procedure(SP) has 2 Parameters defined
 @OfficerID
 @DatasetFlag

The @OfficerID is the Employee # that has to be passeed or ENTERED IN (SSRS Data input form)
The DatasetFlag is the way I control which IF statement is executed in the SP.

<h2>so here is the SP:</h2>

<pre><code>CREATE PROCEDURE [dbo].[StoredProcedureNameHere]
 @OfficerID VARCHAR(7),
@DatasetFlag VARCHAR(60) 

WITH RECOMPILE
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SET NOCOUNT ON;

BEGIN TRY
IF @DatasetFlag = 'EmployeeName'
 Begin
 SELECT [EmployeeName]
 FROM [DatabaseName].[scema].[Employee]
 where EmployeeBNumber = @OfficerID
 END
ELSE
IF @DatasetFlag = 'Expect'
 Begin
 SELECT 
 [TerritoryName]
 ,[TestNumber]
 ,[RuleNumber]
 ,[Expectation]
 ,[TestCount]
 ,[PercentToGoal]
 FROM [Database].[scema].[Table2]
 WHERE OfficerID = @OfficerID
 ORDER BY TerritoryID
 ,TestNumber
 ,RuleNumber
 END

RETURN
GO
</code></pre>

The REPORT has 2 Datasets, one I create and it will pull in the parameters and EmployeeName
the other I create and it pulls in EmployeeName as SSRS can only use 1 Result SET~!
BUT.... I fool it ....

but I simpley CREATE the FIELDS by OVERTYPING the EMPLOYENAME and then ADD the
rest (Query) So Edit the FIELDS in the PROPERTIES of the DATASET and put in the Select Column Names.

Then I ese the PARAMETERS menu (Dataset Properties) to put in an EXPRESSION of ="EmployeeName"
for the first Dataset and ="Expect" for the 2nd. I match up the @OfficeID on that screen too.

then when I run this... it asks for OfficerID (RS creates the input form)
and when I put in the ID and hit VIEW REPORT. Or we can all the RDL with the OfficerID, just like the SSRS form does, but in an ASPX page.

BOTH DATASETS are returned (it calls it twice is my assumption)

So NO whacky FILTERING on a UNION dataset or other tricks that I have to then deal with in SSRS, which is not fun... (seriously IIF?)

I have see one Stored Procedure where we have like 20 Parameters , so you can filter
the OUTPUT of the report at the SQL level, rather than a monster pull and filter at the report.

No can you simply create 20 Stored Procedures, WELL YES, but this way, all the code is in ONE locations, and of course it can use selects into TEMP Tables to merge tons of stuff,
and in the end simply POPULATE a table that is the RESULT SET..

As a programmer I find SSRS a bit whacky, but so far I am having fun, trying to find ways to get what I want, not what it offers...

blocks|key|434380|text|这里有一个技巧可以解决这个问题。这遵循了Union+All一个表的所有结果的想法，并使用SSRS+repost+with+table|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|434381|对于每个单独的查询，添加一个列，其中显示查询的目的，例如“姓名”或“地址”，对于查询的每一行重复该信息。|434382|然后对想要的查询进行Union+All。|434383|在Visual+Studio+/+SSRS报告中:添加包含storedprocedure的数据集。然后从tools中选择tablix并将所需数据拖动到tablix列。然后转到tablix行属性->行可见性。for+there使排序子句带有IFF函数，以便在查询时仅显示具有先前定义的额外列的行，例如“名称”|434384|然后创建第二个tablix并遵循相同的方法，现在使用带有"Address“的IIF函数，然后继续使用所需的多个不同的表。|434385|entityMap^0|K|9|0|0|A|9|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|S|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|T|8|@$9|U|A|V|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|W|8|@]|D|@]|E|$]]|$1|L|3|M|5|6|7|X|8|@]|D|@]|E|$]]|$1|N|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|O|$]]

Here is one trick for this. This follows idea to <code>Union All</code> all the results for one table and works with SSRS repost with table
For each separate query add one column where is showing purpose of the query For e.g. &quot;name&quot; or &quot;address&quot;, this info is repeated for each line of query.
Then <code>Union All</code> the wanted queries.
At Visual Studio / SSRS reporting: Add the dataset which contains storedprocedure. Then from tools select the tablix and drag the wanted data to tablix columns. Then go to tablix row properties -&gt; Row visibility. for there make the sorting clause with IFF function to show only rows with previously defined extra column at queries, for e.g. &quot;Name&quot;
Then make the second tablix and follow the same, now use the IIF function with &quot;Address&quot;, then continue as many different tables needed.

I have a stored procedure that returns multiple resultsets just as below
<pre><code>CREATE StoredProcedure sp_MultipleDataSets
AS
BEGIN
 SELECT EMPID, ENAME, JOB, SAL, DEPTID FROM EMP -- first result set
 SELECT DEPTID, DNAME, LOC FROM DEPT --second result set
END
</code></pre>
In BIDS, while creating a new report I configured the stored procedure for dataset. It creates the dataset ONLY with the columns returned from the first result set. It does not identify the second result set.
How can I create datasets for both the result sets from a stored procedure like above

Multiple Datasets from Stored Procedure in SSRS

Visual Studio

我有一个返回多个结果集的存储过程，如下所示CREATE StoredProcedure sp_MultipleDataSetsASBEGIN    SELECT EMPID, ENAME, JOB, SAL, DEPTID FROM EMP -- first result set    SELECT DEPTID, D...

问SSRS中存储过程中的多个数据集
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SSRS中存储过程中的多个数据集EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SSRS中存储过程中的多个数据集
EN