6

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?

David Ruiz
  • 383
  • 1
  • 4
  • 10
  • This seems to some info :https://blogs.msdn.microsoft.com/vinaysingh/2016/08/29/logic-apps-sql-connector-working-with-stored-procedures/ – TheGameiswar Apr 19 '18 at 18:38
  • That is similar, but for stored procedures. I am looking for the connector that allows running SQL queries directly. – David Ruiz Apr 20 '18 at 02:55

2 Answers2

16

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']
Andrew
  • 7,602
  • 2
  • 34
  • 42
  • 1
    Works perfectly – David Ruiz May 15 '18 at 23:49
  • 1
    you'd think there'd be some clear documentation somewhere on Microsoft's site for this. Not that I could find. Thanks @Andrew – PBMe_HikeIt Feb 13 '19 at 22:04
  • 1
    @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. – John Drinane Mar 22 '19 at 17:04
  • @Andrew, How to check the resultsets object is null or not? – Pradeep Mar 08 '22 at 14:14
  • 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']```. – Andrew Mar 08 '22 at 14:44
3

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.

Johns-305
  • 10,908
  • 12
  • 21
  • 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? – David Ruiz Apr 23 '18 at 13:42
  • @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? – Johns-305 Apr 24 '18 at 01:44
  • 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. – David Ruiz Apr 24 '18 at 21:26
  • @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. – Johns-305 Apr 25 '18 at 01:41
  • @Johns-305 Can you elobarate what you mean by when you say that it performs better when using a stored procedure? – Oliver Nilsen Dec 02 '20 at 19:54
  • @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. – Johns-305 Dec 24 '20 at 15:05