Stroed Procedure Parameters for SSRS

Question

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

Answer 1

Instead of using string_split, you can implement your own string-splitter. This short article on my web site includes a few simple approaches: https://www.sommarskog.se/arrays-in-sql.html.

Answer 2

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?

Answer 3

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,
Aniya