How to use multi value parameter in dynamic sql SSRS report? RRS feed

  • Question

  • Hi, I am trying to use dynamic sql to develop a report. I use parameter which I populate from Territory entity and I want users to be able to choose multiple values. If user does not choose any value, I want to skip filtering and that's why I declare sql variable and build query in it as text. I can than examine the value of Territory parameter and if it contains some values, I can append condition as text to sql variable. At the end I call exec(@sql).

    Problem is that when I try to insert 'in' statement, the query fails:

    set @sql = 'select.... from ... where territoryid in (' +@Territory+')'

    I get incorrect syntax message. I tried with double and triple quotes as well as some suggested solutions with REPLACE function, but nothing worked.

    Any suggestion is welcome

    Friday, June 24, 2016 11:16 AM

All replies