Use Array in Parameterized Query - SQL Server

(0) Share

Report

Posted on by JeffGimo

221

I am trying to pass an array data Grade to my flow. But it only read it as one string.

replace(replace(replace(string(triggerBody()?['Grades']), '["', ''''), '"]', ''''), '","', ''',''')

select * from [SchoolOutreach-Grades] where Grade_Desc in (@Grade);

Here's a sample run:

input:

Then 0 output because it cannot query Grades because it reads those 2 values as 1 whole string.

Here's the raw input.

 "body": {
 "actualParameters": {
 "Grade": "'Grade 2','Grade 5'"
 },
 "formalParameters": {
 "Grade": "nvarchar"
 },
 "query": "select * from [SchoolOutreach-Grades] where Grade_Desc in (@Grade);"

However, the query works if I move the data transformation at the query itself. But this is not desirable because it will be prone to SQL Injection attack.