Passing MultiValue paramaters to a Stored Procedure in Reporting Services.
I have been working on a Reporting Services report that contained a MultiValue select list as one of the parameters and I needed to pass this comma separated list of values to my stored procedure. I had prepared my stored procedure so that it contained a user defined function to split out the values into a table and had tested my stored procedure independently of the report. My problems occurred when I tried to run my report and select multiple from my list. I would end up with the following error – ‘must declare scalar variable @VarName’. To confuse things a little further, if I just selected one item from the MultiValue list then my report would run fine.
The answer, according to other enlightend ssrs users, comes from the method used to define your dataset. I had originally defined my dataset using the Text Command Type to call the stored procedure. E.g. EXEC myStoredProc @MultiValueParam. To get stored procedures to use the multivalue paramaters the dataset needs to be created with a Stored Procedure Command Type and then define the parameters within the Parameters tab.
After making the change my report would run successfully using my multivalue prameters.
Advertisements
