Stroed Procedure Parameters for SSRS

RoyB09 306 Reputation points


I'm using SQL server and SSRS 2012.

In the SSRS report I have a text parameter which Allows Multiple Values selection. When I select multiple values e.g. 'Untied States, France, Germany', the In parameter in the stored procedure i.e.

WHERE CountryName In (@CountryName)

gets the above string passed in as WHERE CountryName In ( 'Untied States, France, Germany' ) and not the following as expected WHERE CountryName In ( 'Untied States', 'France', 'Germany' )

I need some way to return 'Untied States', 'France', 'Germany' from the original string of 'Untied States, France, Germany' ,

Because I'm using SQL 2012, I don't have the STRING_SPLIT function, I also tried using JOIN function in the SSRS DataSet properties JOIN(Parameters!CountryName.Value,","), still can't get it to work

It's been a few years since I used SSRS, but I can't recall it being this difficult to pass a multi valued string parameter???

Thanks in advance

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,793 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

2 additional answers

Sort by: Most helpful
  1. Olaf Helper 40,656 Reputation points

    SSRS already pass the selected values of a "Multi Value Parameter" as comma separted list, as you are looking for and it works with the IN SQL operator.
    When you get something different, then the parameter is setup different as you describe. Is it may a single text where you enter some values comma separated?

  2. AniyaTang-MSFT 12,306 Reputation points Microsoft Vendor

    Hi @RoyB09
    I found some old-fashioned solutions: split-function-equivalent-in-t-sql.
    I tried this, and it worked very well.

    DECLARE @xml xml, @str varchar(100), @delimiter varchar(10)  
    SET @str =(select countryname from dbo.countryname)  
    SET @delimiter = ','  
    SET @xml = cast(('<X>'+replace(@str, @delimiter, '</X><X>')+'</X>') as xml)  
    SELECT C.value('.', 'varchar(50)') as value FROM @xml.nodes('X') as X(C)  

    Best regards,

    0 comments No comments