Execute Stored Procedure is not taking multiple values as Input

Question

Hi, 
 I am trying to use "Execute Stored Procedure" smart Service. When I tried to use multiple values in input, it started failing by giving error "Invalid Column Type". For example, I have a stored Procedure on "Client". We have two inputs for the Stored Procedure. One is TaxPayer ID, second is Client Type. So we are Query for Client by Client Id for "Gold" or "Platinum" Client Type. So when we added Client Type as Multiple for {"Gold","Platinum"}. It started giving error. I can not use MNI as in some stored procedures we have multiple inputs with multiple values. Can anyone guide how can I fix this issue?

Sachin · Answer

Hi rishub i agree with Vinay, that execute store procedure will not allow multiple values as input, but to achieve this by passing Joining the array using JoinArray function and in the store proc use this code 
FIND_IN_SET( 
 yourDBField, 
 ( 
 REPLACE 
 (JoinarrayString, '|', ',') 
 ) 
 ) > 0 
regards, 
Sachin

ganeshbabuj · Answer

For MariaDB - Plus we need to make sure there is no comma in the value passed in, when Appian build the query space gets added before all value so use below on where condition on Procedure 
 AND (p_input IS NULL OR p_ input = '' OR FIND_IN_SET(TABLE.COLUMN_NAME, REPLACE(p_input,' ','')) > 0)