Getting Multi-Value parameters to work with Stored Procedures

1. The stored procedure for the report should declare any multi-value parameters as type    nvarchar(max).

2. In the Report Designer, your parameter should have the ‘Allow multiple values’ option ticked (obviously). Also, it helps to set the Data type to ‘Text’ even if it’s actually an integer or something, because we’re going to have to treat it as text later on in the Stored Procedure.

3. In the DataSet that calls the stored procedure, go to the Parameters tab:

4. For each parameter that needs to be a multi-value one, press the Fx button and change the default:
=Parameters!TerritoryMulti.Value

to
=Join(Parameters!TerritoryMulti.Value, ",")

This will just string the multi-values together into a comma separated string.

5. In the database, you need a function that can split comma-separated values back out into a table format. There are lots of variations of this sort of function around, here’s the one I use:

CREATE FUNCTION [dbo].[ufnSplit]

   (@RepParam nvarchar(max), @Delim char(1)= ',')

RETURNS @Values TABLE (Item nvarchar(100))AS

  BEGIN

  DECLARE @chrind INT

  DECLARE @Piece nvarchar(100)

  SELECT @chrind = 1

  WHILE @chrind > 0

    BEGIN

      SELECT @chrind = CHARINDEX(@Delim,@RepParam)

      IF @chrind  > 0

        SELECT @Piece = LEFT(@RepParam,@chrind - 1)

      ELSE

        SELECT @Piece = @RepParam

      INSERT  @Values(Item) VALUES(@Piece)

      SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)

      IF LEN(@RepParam) = 0 BREAK

    END

  RETURN

  END

6. The query inside the stored procedure that uses the multi-value parameter should use it like this:

where sometable.TerritoryID in (select Item from dbo.ufnSplit(@TerritoryMulti,’,’))

… that is, it uses ufnSplit to split the passed values out into a table, and then selects from that table and uses ‘in’ to check for membership.