Stored Procedure LIST method with multiple select/check box choice control

  • 11 June 2020
  • 3 replies
  • 10 views
R
Badge +8

Hi there.  

 

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:

 

Parameters:

 

@pProductArea varchar(50) = NULL ,
 

 

 

Script:

 

select product_area, workgroup, queue from TABLE1

where 

(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.

 

Best,

 

Rob

 

 

 

And I

3 replies

B
Badge +15

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:



"choice1,choice2,choice3,choice4"


 


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.


 

R
Badge +8

Fair enough.

 

Any other thoughts on how to do this?  

 

If not, I'll go back to the business area and tell them it can't be done.

 

Rob

 

 

T
Userlevel 5
Badge +13

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):

 

WITH product_areas

(

   value

)

AS

(   

   SELECT value FROM STRING_SPLIT(@pProductArea, ',')

)

SELECT product_area, workgroup, queue FROM TABLE1

JOIN product_areas pa ON pa.value = TABLE1.product_area

 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings