How to pass comma separated value to advance sql query
Question
Application Type
Reactive

Hi appreciate someone can help me with this

I wish to pull out a list of employee of this grade M1, M2, M3, M4. 

I store the grade range in a table and pass to my advance sql query but the result is always blank

In my query ; i have the following

SELECT {Employee}.* from {Employee} where {Employee}.[grade] in (@Grade)

Grade is text

Thank you

HI Jerah ,

Set expand Inline true.

Best regards

Devendra

Thank you it work but i noticed i need to change my parameter from M1, M2, M3, M4 to  'M1', 'M2', 'M3', 'M4' else i will get syntax error.

Also it is warning me of sql injection if i expan inline to true, any concern ? 

Yes as suggested by vinod and vikas kindly use build safe actions to prevent SQL injection

Hi Jerah,

If you use expand inline true, OS will give warning. Because as per best practice you should avoid expand inline true in query.

But still if its your requirement then you need to sanitize the input list first before passing it to sql.  I think if you use sanitize (use the BuildSafe_InClauseIntegerList and BuildSafe_InClauseTextList functions to build "WHERE column IN (@values)" clauses. ) in that case you don't need to manually set the list as '1'. Also it will be more safe.

https://success.outsystems.com/Documentation/Best_Practices/Performance_and_monitoring/Avoid_expand_inline_parameters_for_dynamic_values

https://success.outsystems.com/Documentation/11/Reference/Errors_and_Warnings/Warnings/SQL_Injection_Warning


Regards

Thank you all for all the input

@Jerah Less elegant solution but I think valid and readable is to load your grades into a list and use a for loop to process through the list making the SQL input the current grade in the for loop.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.