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

5 posts, 0 answers
  1. Dave
    Dave avatar
    2 posts
    Member since:
    Feb 2015

    Posted 08 Feb 2017 Link to this post

    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.

     

     

  2. Nasko
    Admin
    Nasko avatar
    1067 posts

    Posted 09 Feb 2017 Link to this post

    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
  3. Geetha
    Geetha avatar
    1 posts
    Member since:
    Aug 2015

    Posted 17 Mar 2017 Link to this post

    Thanks  .. It helped us solve the issue  .. Very knowledgeable  article
  4. Ian
    Ian avatar
    18 posts
    Member since:
    Nov 2013

    Posted 05 Feb 2018 Link to this post

    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?

  5. Prashanth
    Prashanth avatar
    1 posts
    Member since:
    Apr 2019

    Posted 26 Apr in reply to Dave Link to this post

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