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

1 Answer 1489 Views
General Discussions
Dave
Top achievements
Rank 2
Dave asked on 08 Feb 2017, 05:49 PM
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
Telerik team
commented on 09 Feb 2017, 02:01 PM

Hello Dave,

Thank you for sharing this!

Regards,
Nasko
Telerik by Progress
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Geetha
Top achievements
Rank 1
commented on 17 Mar 2017, 06:06 PM

Thanks  .. It helped us solve the issue  .. Very knowledgeable  article
Ian
Top achievements
Rank 1
commented on 05 Feb 2018, 02:45 PM

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
Top achievements
Rank 1
commented on 26 Apr 2019, 11:33 PM

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

1 Answer, 1 is accepted

Sort by
0
Henrique Duarte
Top achievements
Rank 1
Veteran
answered on 01 Apr 2021, 10:16 PM
Tags
General Discussions
Asked by
Dave
Top achievements
Rank 2
Answers by
Henrique Duarte
Top achievements
Rank 1
Veteran
Share this question
or