在SqlServer Reporting Services查询中组合“like”和“in”？

The following doesn't work, but something like this is what I'm looking for.

以下不起作用,但这样的东西是我正在寻找的。

select *
from Products
where Description like (@SearchedDescription + %)

SSRS uses the @ operator in-front of a parameter to simulate an 'in', and I'm not finding a way to match up a string to a list of strings.

SSRS使用参数前面的@运算符来模拟'in',而我找不到将字符串与字符串列表匹配的方法。

5 个解决方案

#1

There are a few options on how to use a LIKE operator with a parameter.

关于如何将LIKE运算符与参数一起使用,有几个选项。

OPTION 1

If you add the % to the parameter value, then you can customize how the LIKE filter will be processed. For instance, your query could be:

如果将%添加到参数值,则可以自定义LIKE过滤器的处理方式。例如,您的查询可能是:

 SELECT name
 FROM master.dbo.sysobjects
 WHERE name LIKE @ReportParameter1

For the data set to use the LIKE statement properly, then you could use a parameter value like sysa%. When I tested a sample report in SSRS 2008 using this code, I returned the following four tables:

要使数据集正确使用LIKE语句,您可以使用sysa%之类的参数值。当我使用此代码在SSRS 2008中测试样本报告时,我返回了以下四个表:

 sysallocunits
 sysaudacts
 sysasymkeys
 sysaltfiles

OPTION 2

Another way to do this that doesn't require the user to add any '%' symbol is to generate a variable that has the code and exceute the variable.

另一种不需要用户添加任何'%'符号的方法是生成一个包含代码并超越变量的变量。

 DECLARE @DynamicSQL NVARCHAR(MAX) 

 SET @DynamicSQL = 
 'SELECT  name, id, xtype
 FROM dbo.sysobjects
 WHERE name LIKE ''' + @ReportParameter1 + '%''
 '

 EXEC (@DynamicSQL)

This will give you finer controller over how the LIKE statement will be used. If you don't want users to inject any additional operators, then you can always add code to strip out non alpha-numeric characters before merging it into the final query.

这将为您提供有关如何使用LIKE语句的更好的控制器。如果您不希望用户注入任何其他运算符,那么在将其合并到最终查询之前,您始终可以添加代码以去除非字母数字字符。

OPTION 3

You can create a stored procedure that controls this functionality. I generally prefer to use stored procedures as data sources for SSRS and never allow dynamically generated SQL, but that's just a preference of mine. This helps with discoverability when performing dependency analysis checks and also allows you to ensure optimal query performance.

您可以创建一个控制此功能的存储过程。我通常更喜欢使用存储过程作为SSRS的数据源,并且从不允许动态生成的SQL,但这只是我的偏好。这有助于执行依赖性分析检查时的可发现性,还可以确保最佳查询性能。

OPTION 4

Create a .NET code assembly that helps dynamically generate the SQL code. I think this is overkill and a poor choice at best, but it could work conceivably.

创建一个有助于动态生成SQL代码的.NET代码程序集。我认为这是一种矫枉过正,充其量只是一个糟糕的选择,但它可以想象得到。

#2

Have you tried to do:

你有没有尝试过:

select * from Products where Description like (@SearchedDescription + '%') (Putting single quotes around the % sign?)

select * from Products like Description(@SearchedDescription +'%')(在%符号周围加上单引号?)

#3

Dano, which version of SSRS are you using? If it's RS2000, the multi-parameter list is not officially supported, but there is a workaround....

Dano,你使用哪个版本的SSRS?如果它是RS2000,多参数列表不是官方支持,但有一个解决方法....

#4

put like this:

像这样:

select * 
from tsStudent 
where studentName like @SName+'%'

#5

I know this is super old, but this came up in my search to solve the same problem, and I wound up using a solution not described here. I'm adding a new potential solution to help whomever else might follow.

我知道这是超级老了,但是在我的搜索中出现了解决同样的问题,并且我使用了这里没有描述的解决方案。我正在添加一个新的潜在解决方案,以帮助其他任何人可能会遵循。

As written, this solution only works in SQL Server 2016 and later, but can be adapted for older versions by writing a custom string_split UDF, and by using a subquery instead of a CTE.

如上所述,此解决方案仅适用于SQL Server 2016及更高版本,但可以通过编写自定义string_split UDF以及使用子查询而不是CTE来适应旧版本。

First, map your @SearchedDescription into your Dataset as a single string using JOIN:

首先,使用JOIN将@SearchedDescription映射到数据集中作为单个字符串:

=JOIN(@SearchedDedscription, ",")

Then use STRING_SPLIT to map your "A,B,C,D" kind of string into a tabular structure.

然后使用STRING_SPLIT将“A,B,C,D”类型的字符串映射到表格结构中。

;with
SearchTerms as (
  select distinct
    Value
  from 
    string_split(@SearchedDescription, ',')
)
select distinct
  *
from
  Products
  inner join SearchTerms on
    Products.Description like SearchTerms.Value + '%'

If someone adds the same search term multiple times, this would duplicate rows in the result set. Similarly, a single product could match multiple search terms. I've added distinct to both the SearchTerms CTE and the main query to try to suppress this inappropriate row duplication.

如果有人多次添加相同的搜索词,则会重复结果集中的行。同样,单个产品可以匹配多个搜索字词。我已经为SearchTerms CTE和主查询添加了不同的功能,以尝试抑制这种不适当的行重复。

If your query is more complex (including results from other joins) then this could become an increasingly big problem. Just be aware of it, it's the main drawback of this method.

如果您的查询更复杂(包括其他联接的结果),那么这可能会成为一个越来越大的问题。请注意,这是这种方法的主要缺点。

#1