Use the OData Filter for Azure SQL Databases

2019-09-19 | Toni Pohl

A very quick tip today: When using an Azure Logic App in combination with an Azure SQL Database, filtering rows can be done like here.

As example: A T-SQL query as this one…

SELECT * FROM [dbo].[Customers] WHERE [StorageDataSentDate] IS NULL ORDER BY [Id]

…will be formulated as filter expression with an OData query as here.

image

The definition for the Get_rows_(V2) action looks similar as here:

… "Get_rows_(V2)": {
"inputs": {
"host": {"connection": {"name": "@parameters('$connections')['sql']['connectionId']"} },
"method": "get",
"path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('[someserver].database.windows.net'))},
  @{encodeURIComponent(encodeURIComponent('governancetoolkit365'))}/tables/
  @{encodeURIComponent(encodeURIComponent('[dbo].[Customers]'))}/items",
  "queries": {"$filter": "(StorageDataSentDate eq null)", $orderby": "Id"}
},
"runAfter": {"Initialize_variable": ["Succeeded"]},
"runtimeConfiguration": {
  "paginationPolicy": {"minimumItemCount": 5}
},
"type": "ApiConnection"
}, …

Depending on the data size, remember to configure pagination.

The output can be processed in an ForEach action as needed.

…"For_each": {
"actions": {
"Append_to_string_variable": {
"inputs": {
   "name": "Helper",
   "value": "@{items('For_each')?['StorageDataSentDate']}, @{items('For_each')?['Domain']}"
},
"runAfter": {},
"type": "AppendToStringVariable"
}
},
"foreach": "@body('Get_rows_(V2)')?['value']",
"runAfter": { "Get_rows_(V2)": ["Succeeded"] },
"type": "Foreach"
},…

Happy developing Azure Logic Apps!

Categories: Azure, Cloud, Developer, English, Microsoft, Tools, App, Logic apps

Source: https://blog.atwork.at/post/2019/09/19/Azure-Logic-Apps-with-OData-Filter