Can Azure Logic Apps call a stored procedure with multiple select statements?

Question

I am trying to find out if Azure Logic Apps are capable of iterating through select statements in a stored procedure. I have created my logic app that is passing in one string to pass to the stored procedure.

This logic app is working because I am getting the results of my first select statement but I am not able to reach the rest.

Example of my SQL:

SELECT AIP.*
FROM Item_Price AS AIP
WHERE IP.id = @Parameter

SELECT AII.*
FROM Item_Identification AS II
WHERE II.id = @Parameter

SELECT CP.*
FROM Catalogue_Price AS CP
WHERE CP.id = @Parameter

Currently I am getting back a body

{
  "ResultSets": {
    "Table1": [
      {
        "id": "123",
        "price": "123.00"
        "name": "test Item"
      }
    ]
  },
  "OutputParameters": {}
}

I am missing results from the next two select statements in my stored procedure, is there a way I can iterate through and get the rest of my data or are Logic Apps not able to do this action?

Answer 1

I do agree that if you use on-prem sql then only the first select statements output will be returned as this is a limitation of the connector.

Multiple result sets support is currently limited for SQL native query execution. Only result sets, up to the first empty one result set, are returned

If you do not use a on-prem then you will get output with all returns and below is my design:

Sql Stored Procedure:

CREATE PROCEDURE RithTest
AS
BEGIN
    SELECT TOP (1000) * FROM [dbo].[T3] AS RR WHERE RR.id = 2;
    SELECT TOP (1000) * FROM [dbo].[T2] AS BC WHERE BC.SNO = 2;
    
END;

Output:

{
  "Table1": [
    {
      "ID": 2,
      "Month": "2024-05-01T00:00:00",
      "Condition1": "2024-05-01T00:00:00",
      "Condition2": "2024-05-31T00:00:00"
    }
  ],
  "Table2": [
    {
      "SNO": 2,
      "Name": "Rithwik"
    }
  ]
}