I'm trying to create something where we can filter by multiple selections using a check box list from the "choice" control. I am thinking that if it's possible, we'll have to use a list stored procedure.
So, my stored procedure looks something like this:
@pProductArea varchar(50) = NULL ,
select product_area, workgroup, queue from TABLE1
(product_area in (@pproductarea) or @pproductarea is null)
I have the product area parameter being passed into the stored procedure via the "CHOICE" control with the delimiter being COMMA (,) It works when a single selection is made, but if multiple selections are made, the result set comes in blank.
Any thoughts on this? Is it even possible? I am thinking there must be SOME way to do this, otherwise why would we have the Choice control with the ability to change the delimeter...
Any help is appreciated.
This is the first time I am seeing someone using the choice control in this manner. I don't think this is going to work.
The Choice control, with multiple selections enabled, will contain a string value that looks something like this:
This is why I'm guessing your stored procedure is not working propery, because @pproductarea is not storing a list, but a string of value.
I think your stored procedure needs to take the comma-delimited list of parameters and put them into a temp table or something you can query against. Something like this (will almost certainly need tweaks):
SELECT value FROM STRING_SPLIT(@pProductArea, ',')
SELECT product_area, workgroup, queue FROM TABLE1
JOIN product_areas pa ON pa.value = TABLE1.product_area