How to set optional parameter from Azure API Management and sent this to Azure Logic App and accept by SELECT action

Question

The Logic App executes a SELECT statement and returns me some rows, and I'm using Azure API management to expose these rows to external users as an API. I need two optional parameters (Ex. StartDate & EndDate) to filter out the rows in the SELECT statement.

1. How to set these optional parameters in the API (Azure API Management)?
2. Once set, how to pass these to the SELECT statement WHERE condition inside the logic app?

Answer 1

Hi Vivek!

Thanks for reaching out!

What you can do is add query parameters to the APIM with the following steps:

1. Find the endpoint in your APIM
2. Click the 'pencil' to edit

3. Select the 'query' tab
4. Add the query parameters, for example "start-date" and "end-date"

After you did this you can send the query parameters through APIM which are ready for the LA to be consumed.
You can reference the query parameters in your Logic App using the codes below and use them as desired in your SELECT statement:

However, you say you want optional parameters referenced in your SELECT statement, which makes it not able to add it directly.
What I like to do is the following:

1. Add an Array variable in your Logic App called 'sqlQuery'
2. Add a condition with the following code for the start date, checking if it not is equal to null coalesce(triggerOutputs()?['queries']?['start-date'])
3. Add a append-to-array action in the true part of the condition containing the following for the start date StartDateTime >= '@{triggerOutputs()['queries']['start-date']}'
4. Add another condition for the end date with the following code, again not being equal to null coalesce(triggerOutputs()?['queries']?['end-date'])
5. Add another append-to-array with the following code for the end date StartDateTime <= '@{triggerOutputs()['queries']['end-date']}'
6. Add the following code which basically checks if there were any statements added to the sqlQuery variable, and if so concat 'WHERE' to start the where-clause and afterwards join the sqlQuery array with ' AND ' to make sure the where-statements for a valid query if(greater(length(variables('sqlQuery')),0),concat('WHERE ',join(variables('sqlQuery'),' AND ')),'')

Also, find the image below for your reference: