SSRS Multiple Select Parameter For LIKE SyntaxPosted: October 23, 2013
I came across this question from this thread at MSDN forum:
After a little research, I came up with a solution to work with a stored procedure. There are two options available for the stored procedure: one is a UDF Split function with a JOIN … Like and the other is to build the query dynamically.
When you use stored procedure for your Datasets, you need to add columns as fileds for the Datasets manually. Of course, you need to set the multiple Select parameter from Parameter list first.
I hope you can follow along with this solution (Two Stored Procedures in the following)
CREATE PROCEDURE mysp @ProjDept nvarchar(2000) as Begin declare @mylike nvarchar(2000)='' DECLARE @Sql NVARCHAR(MAX) SET @mylike=' ProjectDept Like ''%' + REPLACE( @ProjDept,',', '%'' OR ProjectDept Like ''%') +'%''' SET @Sql = N'SELECT ProjectName,[Owner] FROM ProjectTable Where ' + @mylike EXEC sp_executesql @sql End
Create PROCEDURE [dbo].[mysp2] @s nvarchar(2000) as Begin SELECT * from projectTable pt INNER JOIN dbo.Split(@s,',') fs ON pt.ProjectDept + fs.s + '%' End --The s is the column name returned from the SPLIT function I used