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?

Solution 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']

Solution 2

Stored Procedures are always better for many reasons and the output can be reasonable well inferred by the Connector. That's why Stored Procedure output lights up in the designer.

Execute SQL Actions return 'untyped' content which is why you don't see specific elements in the designer.

To use the Execute SQL output like a Stored Procedure output, you would have to define the JSON Schema yourself, and use the Parse JSON Action to light up the SQL output.

Comments

[David Ruiz]

Thanks. In what step would I define the JSON Schema? And would it be in Logic Apps? Or do you mean I should define the SQL Query to return JSON output directly and then parse it?

[Johns-305]

@DavidRuiz To be perfectly clear, it is much easier, more reliable, more maintainable and to use a Stored Procedure. It will also perform better. Why do you not want to use SP's?

[David Ruiz]

I understand. The issue is that I may not always have access to create stored procedures in this database, so I would like to be able to use SQL queries without depending on the stored procedures.

[Johns-305]

@DavidRuiz Cross that bridge when you have to...and you probably won't. For now, do it right and only compromise if there's no other option.

[David Ruiz]

Works perfectly

[PBMe_HikeIt]

you'd think there'd be some clear documentation somewhere on Microsoft's site for this. Not that I could find. Thanks @Andrew

[John Drinane]

@PBMe_HikeIt dude I think at some point MS phoned it in on their documentation... I feel like it used to be MS isn't cutting edge, but well documented and if you wanted cutting edge it was undocumented open sores. Now it's all open sores.

[Oliver Nilsen]

@Johns-305 Can you elobarate what you mean by when you say that it performs better when using a stored procedure?

[Johns-305]

@OliverNilsen Because sp execution plans can be cached and optimized over time. Dynamic SQL is seen by the engine for the first time, every time. This has always been the case and it's what SPs are for, just basic SQL.

[Pradeep]

@Andrew, How to check the resultsets object is null or not?

[Andrew]

It's been a long time not working on this, but I guess one option is to use the null-coalescing operator in each following index, like ['resultsets']?['Table1']?[0]?['Date'].