We help IT Professionals succeed at work.

SSRS pass multiple values parameter as single string to stored procedure

MadIce
MadIce asked
on
114 Views
Last Modified: 2019-09-29
I have a store procedure that has a parameter of varchar(max). this parameter is use to build the where clause for an "IN" statement. example user provides one of the following"
ABCD, ABBB, ADDD
and so forth. or they can pass as returns
ABCD
ABBB,
ADDD
The store procedure works. Problem is the parameter when passing through SSRS. If I leave as is. The comma delimited way works. but user can't really see the list and their list may not be comma delimited. When I change the data type in the report parameter properties to "allow multiple values" the stored procedure doesn't like it. I get error:
"must declare the scalar variable "@parameter".
My guess its trying to send as multiple parameters as oppose as one string.
Hope this make sense. Any ideas? using SSRS 2008 and SQL Server 2008
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
In the SSRS report, on the parameters tab of the query definition, set the parameter value to

=join(Parameters!<your param name>.Value,",")
In your query, you can then reference the value like so:

where yourColumn in (@<your param name>)

Author

Commented:
lcohan, sorry for slow response. was unable to connect all day yesterday. Not sure where to put the code =join(Parameters!<your param name>.Value,","). I'm using bids 2008 to build the report. I've attached an image of the Report Parameter Properties. Is somewhere in here where I add it?

Side note question. Can I use a later version of Bids and still run report on SSRS 2008?
ParmSSRS.jpg

Author

Commented:
found a different solution not related to question.
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions