none
Incorrect syntax near ',' with multi-valued parameter in SSRS

    Question

  • I have a report that calls a view. In the report's data section, I use a where in clause just like the help file states. However, when I choose more than one value from the multi-valued box, it gives me the following error.

    An error occurred during local report processing.
    An error has occurred during report processing.
    Query execution failed for data set 'DATASETNAME'.
    Incorrect syntax near ','.

    The where clause is like this.

    WHERE CustomerName In (@CustomerNameParam)

    If I select one item from the multi-valued box it works just fine. What am I doing wrong?
    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    Wednesday, January 27, 2010 8:54 PM

Answers

  • Hi Nathon,

    When the parameter is a multi-select you get a string like this: 'ParamValue1,ParamValue2,ParamValue3', while if it is a single select the string is: 'ParamValue1'. You want to say: WHERE paramName IN ('ParamValue1','ParamValue2','ParamValue3'), so you have to split the string prior to using it like this.

    I would normally build a function in SQL, which will do this. Something like udf_t_SplitString(@String), which is a table valued function breaking up the comma-delimited string. Then, you can use it like this: WHERE ParamName IN (SELECT ParamValue FROM udf_t_SplitString(@Param)). This should give you the correct result.

    Note: The issue does not exist for single selects because the param value is already in the correct format: WHERE ParamName IN ('ParamValue1').

    Also, make sure that the nvarchar/varchar for the sproc parameter is long enought to take all selections.

    Boyan Penev --- http://www.bp-msbi.com
    • Marked as answer by Nathon Dalton Wednesday, January 27, 2010 11:40 PM
    Wednesday, January 27, 2010 11:00 PM

All replies

  • Hi Nathon,

    When the parameter is a multi-select you get a string like this: 'ParamValue1,ParamValue2,ParamValue3', while if it is a single select the string is: 'ParamValue1'. You want to say: WHERE paramName IN ('ParamValue1','ParamValue2','ParamValue3'), so you have to split the string prior to using it like this.

    I would normally build a function in SQL, which will do this. Something like udf_t_SplitString(@String), which is a table valued function breaking up the comma-delimited string. Then, you can use it like this: WHERE ParamName IN (SELECT ParamValue FROM udf_t_SplitString(@Param)). This should give you the correct result.

    Note: The issue does not exist for single selects because the param value is already in the correct format: WHERE ParamName IN ('ParamValue1').

    Also, make sure that the nvarchar/varchar for the sproc parameter is long enought to take all selections.

    Boyan Penev --- http://www.bp-msbi.com
    • Marked as answer by Nathon Dalton Wednesday, January 27, 2010 11:40 PM
    Wednesday, January 27, 2010 11:00 PM
  • Yea, that's what I figured I'd have to do. It just bothers me that the help files explicitely state for multi-valued parameters to use the where clause that I used... and it doesn't work.

    This help page:

    ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/rptsrvr9/html/1ef72267-f727-417f-80d8-593bcffb45bd.htm

    Shows this example:

    SELECT FirstName, LastName, e.Title
    FROM HumanResources.Employee AS e
        JOIN Person.Contact AS c 
        ON e.ContactID = c.ContactID
    WHERE e.Title IN (@Title)
    Just seems wierd that they wouldn't test it. Thanks for the help! I'll get started writing the function.
    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    Wednesday, January 27, 2010 11:40 PM
  • Found this one on one of the servers here. Should work..

    CREATE FUNCTION [dbo].[TvfMVParamSplit]
       (@RepParam varchar(8000), @Delim char(1)= ',')
    RETURNS @Values TABLE (Param varchar(8000))AS
      BEGIN
      DECLARE @chrind INT
      DECLARE @Piece varchar(8000)
      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(Param) VALUES(Cast(@Piece AS varchar(8000)))
          SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
          IF LEN(@RepParam) = 0 BREAK
        END
      RETURN
      END

    Boyan Penev --- http://www.bp-msbi.com
    Wednesday, January 27, 2010 11:44 PM
  • I have a SQL CLR .NET library that I use to provide more rich functions to SQL. I put a SplitString function in it that accepts two string parameters. The first is the string to be split and the second is the delimiting character. It then splits it, turning it into rows in a new SQL table, and returns the table. It works great!

    Thanks for all your help!


    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    Thursday, January 28, 2010 5:37 PM
  • Boyan,

    I edited your code (it didn't work when I tried it) and made it work... below is what I wrote; change it as it may be useful to you... obviously this would have to be created into a function of your choice:

     

    declare

     

     

    set

     

     

    declare

     

     

    declare

     

     

    set

     

     

    set

     

     

     

    DECLARE @chrind INT

     

     

    SET @Piece = ''

    SELECT @chrind = 1

    WHILE @chrind > 0

     BEGIN

     

     

     

     

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

     

     

     

     

     

     

     

    END

    -- RETURN

    select

     

     


    :)

     

     

     

     

     

     

     

     

    @Piece automatic
    IF LEN(@RepParam) = 0 BREAK
    SET @RepParam = SUBSTRING(@RepParam,(@chrind + 1),(LEN(@RepParam) - @chrind))
    SELECT @Piece = @Piece + '''' + @RepParam + ''''
    ELSE
    IF @chrind > 0
    SELECT @chrind = CHARINDEX(@Delim,@RepParam)
    DECLARE @Piece varchar(8000)
    @RepParam = @param
    @Delim = ','
    @Delim char(1)
    @RepParam varchar(8000)
    @param = 'ARC,MTR,NKA'
    @param as char(3000)
    Tuesday, May 04, 2010 9:14 PM