MadIce
asked on
SSRS pass multiple values parameter as single string to stored procedure
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
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
Last Comment
ASKER
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
Side note question. Can I use a later version of Bids and still run report on SSRS 2008?
ParmSSRS.jpg
ASKER
found a different solution not related to question.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SQL
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
=join(Parameters!<your param name>.Value,",")
In your query, you can then reference the value like so:
where yourColumn in (@<your param name>)