SSRS Multiple Select Parameter For LIKE Syntax


I came across this question from this thread at MSDN forum:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3e9400ca-cfc3-4f66-8b48-d7e0ee61d969/sql-in-and-like-in-a-sinlge-query-for-ssrs-report?forum=transactsql

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
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s