How to retrieve data from a stored procedure called via Power Automate?

(0) Share

Report

Posted on by lesotho_kid

106

Hello,

We have a PowerApps form which can only display data (based on a SQL stored procedure) for a single record - which is filtered by parameters identified via QueryString variables..
(1) We have the variables set in the OnStart of App. They work.

(2) We have the PowerAutomate working - tested in PowerAutomate. It works.

(3) I'm not sure how to call the PowerAutomate flow from PowerApps. Do not want this to occur on a button click. Needs to be populated as the user opens the report - so preferably OnStart of App. Is that possible? And there are multiple fields in the stored procedure dataset which is returned. What type of object should this be stored in so it can then be accessed via text boxes, etc?

I guess as a related question - is there some way we can just write code to do this within PowerApps? Can you embed C# or VB.Net in PowerApps? This would be simple in various other apps - but seems unnecessarily complicated in PowerApps/PowerAutomate. We are being asked to test out PowerApps which is why we are trying it out using this tool - otherwise would just code it.

Appreciate any help you can offer.

Thanks,

Dan

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

lbendlin 8,726 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Have you gone through this example? Calling a SQL Server Stored Procedure from Power Apps (mssqltips.com)
ANB 7,252 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @lesotho_kid , The output of your store procedure can be return to PowerApps using 2 actions:

Using Respond to a PowerApp or flow action which does not require any premium license but you have to pass in below format. But there is drawback of this action, if you have output of SP as an array then making it in PowerApps become difficult because you have to pass the array as string and then convert in array again app.

So, there is alternate action named as Response action which require premium license.

Using this action, you can even pass a proper JSON object and then use it in app as collection. Please check this.

So, yes using the app OnStart property you can trigger a Power Automate which further trigger your SP and whatever is the output you can store that in variable or collection in app. Example syntax:

ClearCollect(col_OutputofSP, FL_SP.Run(Parameter1,Parameter2,...ParameterN))

Regarding, embedding C# or VB.Net in PowerApps question, as per my knowledge you can consume or integrate Web API by creating custom connectors. Please check this:

https://learn.microsoft.com/en-us/shows/on-net/integrating-powerapps-with-net-web-apis

https://learn.microsoft.com/en-us/connectors/custom-connectors/

-----------------------------------------------------------------------------------------------------------------------------

I hope this helps.

Please click Accept as solution ✅ if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs up.👍

Thanks,
ANB
lesotho_kid 106 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @ANB - I tried that but it is still not working.
This is the power automate stored proc call - which has been tested from Power Automate and works. Note - hard-coded parameters to set up but in production need to pass in parameters from PowerApps

And this is from the App OnStart - PowerApps does not seem to recognize the Power Automate flow?
Appreciate your help with this.

Thanks
Dan
Verified answer

ANB 7,252 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @lesotho_kid , As per the screenshot that you gave, you haven't used the Response action after your Store procedure action.

Please my screenshot:

Whatever is the output of your Store procedure, add that as payload to generate the schema. Then save the flow and refresh the flow in app also and check the behavior.

-----------------------------------------------------------------------------------------------------------------------------

I hope this helps.

Please click Accept as solution ✅ if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs up.👍

Thanks,
ANB
lesotho_kid 106 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @ANB - getting closer! Now the flow works when I test - have the response and there are values in it - from Power Automate. And PowerApps can see the flow. But, when I connect to the flow from PowerApps I'm getting the error: JSON parsing error expected array but got object. Any idea what how to resolve. Have been searching online but unable to find answer yet.

Thanks,
Dan
lesotho_kid 106 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello,

Thanks @ANB
Figured this out. There were a few issues: (1) when you use the PowerApps tool to create the schema 'automatically' - it doesn't work properly. You need to delete the 'Required' fields. (2) You need to reference the Resultset.Table from the Body section of the Response object. But, this isn't automatically allowed - just allows you to select 'Resultset'. So you need to type it in instead of selecting from the dropdown options. See this article for more details Return an Array from Flow to PowerApps (Response Method) | Microsoft Power Apps
Overall, if anyone is considering using PowerApps to call stored procedures would STRONGLY RECOMMEND not doing so. At least for this task, this is a horribly convoluted tool (actually 2 tools: Power Apps and Power Automate) needed to do 1 reasonably simple thing. The processes are not clearly documented and much of the basic functionality needed does not work properly without altering the auto-generated code or automated options provided by the given apps. I used Microsoft Access >= 25 years ago and it could accomplish this simple task much better than these tools.