Azure Logic Apps'执行SQL查询'连接器

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.

我正在尝试实现几个查询/更新Azure SQL Server数据库的Azure Logic应用程序。查询返回一个值或具有多行的表。我不想创建存储过程，而是使用“执行SQL查询”连接器。我的查询在Logic Apps中正常运行，但我还没有找到一种方法来提取要在后续步骤中使用的查询的输出，或者在HTTP响应中返回。

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

有人可以指导我如何为单值和表输出做到这一点？

2 个解决方案

#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:

如果由于某种原因您不想创建SP，或者无法创建SP，则可以在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:

如果找不到数据的确切“路径”，请运行并让它失败。然后检查失败的步骤，在“显示原始输出”中，您将能够看到执行SQL查询步骤的结果。例如：

{
  "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']

#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.

执行SQL操作返回'无类型'内容，这就是您在设计器中看不到特定元素的原因。

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.

要像存储过程输出一样使用Execute SQL输出，您必须自己定义JSON模式，并使用Parse JSON Action来点亮SQL输出。

#1