FAQ: How do I pass a multi-value parameter to a dataset while using LIKE operator in the WHERE clause?

  • Article

Question

 In my report, there is a single-selected parameter called rptParameter and it is used in my dataset query like this:  select Col1,Col2,Title from myTable where Title like @rptParameter + '%' , the report works fine. Now, I want to allow multiple values for this parameter, so I check the "Allow multiple values" option for this parameter, but the report fails to run. How can I resolve this issue? 

Answer

 1. Run the following code in your database to create a table valued function which will be used to split a string into a table. 

CREATE FUNCTION [dbo].[fn_String_To_Table] (

            @String VARCHAR(max),

   @Delimeter char(1),  

   @TrimSpace bit )     

RETURNS @Table TABLE ( [Val] VARCHAR(4000) )

AS

BEGIN

    DECLARE @Val    VARCHAR(4000)

    WHILE LEN(@String) > 0

    BEGIN

        SET @Val    = LEFT(@String,

             ISNULL(NULLIF(CHARINDEX(@Delimeter, @String) - 1, -1),

             LEN(@String)))

        SET @String = SUBSTRING(@String,

             ISNULL(NULLIF(CHARINDEX(@Delimeter, @String), 0),

             LEN(@String)) + 1, LEN(@String))

  IF @TrimSpace = 1 Set @Val = LTRIM(RTRIM(@Val))

    INSERT INTO @Table ( [Val] )

        VALUES ( @Val )

    END

    RETURN

END

2. In your repot, define a single valued and internal parameter called MPjoin, then must set its default value using the expression =Join(Parameters!rptParameter.Value,",").

3. Change your dataset query string like this:

SELECT T1.Col1,T1.Col2,T1.Title

FROM myTable T1 INNER JOIN fn_String_To_Table(@MPjoin,',',1) T2

             ON T1.Title LIKE T2.Val+ '%'

4. Open the Dataset Properties, in the windows box, click Parameters to go to the dataset Parameters page and make sure the @MPjoin value is from @MPJoin or the expression =Join(Parameters!rptParameter.Value,",").