Stroed Procedure Parameters for SSRS

RoyB09 306 Reputation points
2022-08-29T11:41:05.893+00:00

Hello

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
Roy

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
Transact-SQL
Transact-SQL
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
    2022-08-29T11:59:09.177+00:00

    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
    2022-08-30T06:16:30.737+00:00

    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)  
    

    235946-1.png235947-2.png
    Best regards,
    Aniya

    0 comments No comments