How to pass multi-value parameters to a Stored Procedure datasource

[Dave]

Today I came across the stumbling block of passing multi-value parameters to a Stored Procedure. After some trial and error, I have discovered a way to do this. I'm posting it here as I don't see any solutions for this problem in the forum already and the Telerik documentation here states it is not possible.

The key is to pass the parameter's multiple values as a comma (or other character) delimited string. To do this, configure your parameter (using a parameter named @department as an example) in the "Configure data source parameters" step of the data source configuration wizard to join the selected values together using the syntax:

=Join(",",Parameters.Department.Value)

 

Now we need a table-valued function in the database to convert the comma delimited list string to a table of values. Here is what I used:

CREATE FUNCTION splitstring ( @stringToSplit VARCHAR(8000) )
    RETURNS
        @returnList TABLE ([Param] [nvarchar] (500))
AS

BEGIN

    DECLARE @name NVARCHAR(255)
    DECLARE @pos INT

    WHILE CHARINDEX(',', @stringToSplit) > 0
    BEGIN
        SELECT @pos  = CHARINDEX(',', @stringToSplit)
        SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

        INSERT INTO @returnList
        SELECT @name

        SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
    END

    INSERT INTO @returnList
    SELECT @stringToSplit

    RETURN
END

 

Now you can utilize the passed parameter in your main report data source SQL query by using the following syntax in your WHERE clause:

WHERE
      @department IS NULL OR
      @department='' OR
      @department='All' OR
      salesman.department IN (SELECT [Param] FROM splitstring(@department))

 

In my example I'm allowing for a supplied NULL, empty string or 'All' value top denote selecting all departments.

Hope this helps some of you out.

 

 

[Nasko]

Hello Dave,

Thank you for sharing this!

Regards,
Nasko
Telerik by Progress

[Geetha]

Thanks  .. It helped us solve the issue  .. Very knowledgeable  article

[Ian]

Thanks for this - it looks a useful alternative to my other solution - using a ObjectDataSource wrapper to build DataSet to pass in TV parameters.

I wonder what the performance limitations in terms of maximum length of CSV string might be?

[Prashanth]

Great!..this clean solution solved my issue at an instant! Thanks Dave :)

[Henrique Duarte]

You should look at this blog post from Oracle:

How to split comma separated string and pass to IN clause of select statement | Oracle ADF Illusions Blog