How to read results of a sql stored procedure in power automate?

[aaedla]

Hi,

Created a store procedure and created a flow to get email ids from SQL table using store procedure. Flow is executing successfully and I can see the results in Execute Store procedure action but I want to read those email accounts and I want to execute another store proc in apply each condition.

How to read out put values from execute store procedure and execute apply each condition action?

Any help would be greatly appreciated.

Thank you.

[SudeepGhatakNZ · Accepted solution]

@aaedla ,

To call the stored procedure for each item returned from your previous action, simply reference the field from the previous action using dynamic content. This will automatically add an "Apply to each" loop for you.

[aaedla]

@SudeepGhatakNZ Thank you for your comments.

And one more question,

how to read response from execute store procedure action?

I want to use if else condition, if condition is true then I want to execute store proc A and if it is false then execute store proc b.

I tried to use Response under Request but somehow I am getting error while saving flow. What is the best way to read the outputs of execute store proc and use it in next action?

Here is the error.

The workflow with 'Response' action type should not have triggers with 'splitOn' property defined: 'When_a_new_response_is_submitted'.'."}}". Code: 0x80060467 InnerError: '.

[SudeepGhatakNZ]

Would you like to show me on a Team's call?

[aaedla]

Not possible to show on teams Sir. It's client laptop.

At least give me some idea so that I can try at my end.

[SudeepGhatakNZ]

Can you post a screenshot of your flow?

[aaedla]

here it is 

[SudeepGhatakNZ · Accepted solution]

Replace Response action with a Compose action

[aaedla]

@SudeepGhatakNZ  if I refer the field from previous step is throwing an error (this is an object, not an array). Then I added parse JSON action to convert object into array but still I am getting null error at apply each loop step.

Any suggestion would be greatly appreciated.

[SudeepGhatakNZ]

Can you share the output of the spGetEmployeeEmail action? I will build you a sample

[aaedla]

Here is the output of store proc.

{
"statusCode": 200,
"headers": {
"Cache-Control": "no-store, no-cache",
"Pragma": "no-cache",
"Transfer-Encoding": "chunked",
"Vary": "Accept-Encoding",
"Strict-Transport-Security": "max-age=31536000; includeSubDomains",
"x-ms-request-id": "a7394be8-22ee-46ae-bf38-fe50a8a91b08",
"X-Content-Type-Options": "nosniff",
"X-Frame-Options": "DENY",
"x-ms-connection-gateway-object-id": "48938ae0-851d-465d-9cb1-ae8f550396db",
"Timing-Allow-Origin": "*",
"x-ms-apihub-cached-response": "false",
"x-ms-apihub-obo": "false",
"Date": "Sun, 24 Mar 2024 03:59:25 GMT",
"Content-Type": "application/json; charset=utf-8; odata.metadata=minimal",
"Content-Length": "2440733",
"Expires": "-1"
},
"body": {
"ResultSets": {
"Table1": [
{
"DisplayName": "LN, FN1",
"Email": "UserFirstName1@xxx.com",
"EmployeeNumber": "0000003",
"AccountName": "XXXX",
"Status": "T"
},
{
DisplayName": "LN, FN2",
"Email": "FN1_LN@xxx.com",
"EmployeeNumber": "0000005",
"AccountName": "XXXX",
"Status": "T"
},
{
DisplayName": "LN, FN2",
"Email": "UserFirstName2@xxx.com",
"EmployeeNumber": "0000007",
"AccountName": "XXXX",
"Status": "T"
}

]
},
"OutputParameters": {}
}
}

[SudeepGhatakNZ · Accepted solution]

[aaedla]

@SudeepGhatakNZ thank you it worked but I am getting warning of resources because of store procedure inside the loop. So I am trying to execute the store procedure outside and get all the 15k records then use output of store proc as input in filter array to filter the data and if it finds a match then execute next action.

But here I am getting error for filter array action and it's asking me to use array instead of object. Below is my input. how to convert this into array?  I used Parse JSON but I am getting wrong schema error. May I know the correct schema to convert object into array? Any suggestion would be greatly appreciated.

{

"Table1": [

{

"Email": "test@test.com"

},

{

"Email": "test2@test.com"

}

]

}