多值和存储过程 [英] mulit value and stored procedures

问题描述

我查看了有关报告服务中多值选择的其他问题，但没有一个解决了我的问题.
我的问题是这个.我有一份报告，其中包含对参数的查询 - @Type.
选择不同(类型)来自类型表按类型排序

I have looked at the other questions concerning Multi Value selection in reporting services and none of them fixed my issue.
My issue is this. I have a report that has a query for the parameter - @Type.
Select distinct(type) from TypeTable order by Type

报表参数设置完毕，报表在选择框中显示所有返回的结果(当前为3个).

the report paramenter is set up and the report shows all the returned results (currently 3) in the selection box.

我的存储过程有以下子句:where Type in (@Type).该报告仅返回选择框中的第一项，而不是所有三项.

My stored procedure has the following clause: where Type in (@Type). The report is only returning the first item in the selection box instead of all three.

请帮忙.

谢谢！！！

推荐答案

我不确定多选列表框返回的格式或数据类型，但如果是这样的:

I'm not sure of the format or data type that is returned by the multi-select list box, but if it is something like:

X,Y,Z

您有两个选择(我更喜欢选项 1):

you have two options (I prefer option 1):

选项 1
您可以使用 SQL Server 2005 中的数组和列表 - 使用数字表.使用该代码，您可以将字符串拆分为一个表，每个值都在自己的行中.然后，您可以将该表 INNER JOIN 到您的查询并根据多个 @Type 值进行过滤.

option 1
You can split it apart using Arrays and Lists in SQL Server 2005- Using a Table of Numbers. Using that code, you can split the string into a table, with each value in its own row. You can then INNER JOIN that table to your query and filter based on the multiple @Type values.

选项 2
在您的存储过程中，您可以将查询动态构建为字符串.然后您可以 EXECUTE (@YourString) 并运行该查询.你的字符串看起来像:

option 2
Within your stored procedure you can build your query dynamically into a string. You can then EXECUTE (@YourString) and run that query. Your string would look something like:

SET @QUERY='SELECT... FROM ... WHERE ... IN ('+ISNULL(@Type,'')+')'

SET @QUERY='SELECT... FROM ... WHERE ... IN ('+ISNULL(@Type,'')+')'

看这个链接动态 SQL 的诅咒和祝福

这篇关于多值和存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！