Azure Logic Apps 'Execute SQL Query' Connector

Question

I am trying to implement several Azure Logic Apps that query/update an Azure SQL Server Database. The queries return either one value or a table with several rows. I prefer not to create stored procedures, but instead use the 'Execute SQL Query' Connector. My queries are running fine in the Logic Apps, but I have not found a way to extract the output of the queries to use in next steps, or return in an HTTP Response.

Can someone guide me on how this can be done for both single-value and table outputs?

Answer 1

If for some reason you don't want to create a SP, or cannot do it, you can access your custom query results by using this in your JSON:

@body('Name_of_Execute_SQL_Query_step')?['resultsets']['Table1'][0]['NameOfYourColumn']

If you can't find the exact "path" for your data, run and let it fail. Then go check the failing step and there in "Show raw outputs" you will be able to see the results of the Execute SQL Query step. For example:

{
  "OutputParameters": {},
  "ResultSets": {
    "Table1": [
      {
        "Date": "2018-05-28T00:00:00"
      }
    ]
  }
}

To access that date, you'd of course need to use:

@body('Name_of_Execute_SQL_Query_step')?['resultsets']['Table1'][0]['Date']