passing multiple parameters in SSRS?

Question

I have this report parameter, named @LeadSource2.

Will it work when I pass it to my final SP that will use in (@LeadSource2) in the code…?

I don’t understand if Stored Procedures do not accept multiple values for a parameter, why SSRS has the option to pass multiple values?!

I have it like this on my report:

And in my SP I do 'where LS in (@LeadSource2).'

Its not showing me results, so I think it does not work…

My question is why SSRS would allow me to do that if the SP wouldn’t accept it?

Have you tried it? How is your sproc set up to receive multiple parameters? Are you expecting one output, many `union`ed together, multiple reports? — iamdave, Feb 27 '17 at 11:21
SSRS doesn't know you're using a sproc, and you can pass multiple values to SQL and to a Data Model. So it makes sense for that functionality to be there even though the sproc won't accept it. In any case; your question seems to be answered here: http://stackoverflow.com/questions/13764010/how-to-pass-multiple-values-to-single-parameter-in-stored-procedure — Dan Scally, Feb 27 '17 at 11:50
Possible duplicate of [How to pass multiple values to single parameter in stored procedure](http://stackoverflow.com/questions/13764010/how-to-pass-multiple-values-to-single-parameter-in-stored-procedure) — Dan Scally, Feb 27 '17 at 11:50
So, basically there is no way I can do inside an SP: in (@parameter). This is forcing me to copy the SPs code to the ‘text’ seccion of the dataset in the SSRS, which doesn’t seem to be like a best practice… — Chicago1988, Feb 27 '17 at 12:18

score 0 · Answer 1 · answered Feb 27 '17 at 11:53

If you were using the in(@LeadSource2) syntax in a direct SQL dataset, it would work fine. Whereas because you are using a sproc, as Dan says, you will need to be more involved.

Use the join expression in your Parameter Values properties window to pass your multi-select values to your sproc as a comma/pipe/whatever separated list and then use a string splitting function to parse it out into multiple values you can use in your sproc.

passing multiple parameters in SSRS?

1 Answers1