Combine 'like' and 'in' in a SqlServer Reporting Services query?

Question

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.

Answer 1

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

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:

 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:

 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.

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.

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.