cancel
Showing results for 
Search instead for 
Did you mean: 

Collect from stored procedure

I would looooove if the collect() function could take an Azure SQL DB stored procedure with params. That would open PowerApps to some very powerful and flexible enterprise scenarios where all the business logic and heavy lifting could be done server-side by the DB.

 

We could limit it to the first resultset returned by the proc to simplify things. The cherry on the top would be to be able to update a record with a proc, but hey, one fight at a time. ^^

 

cheers

Status: Under Review

Updating status and adding @LanceDelano

Comments
Level: Powered On

Ok, I figured this out.  If you don't have the schema defined when you register/add the flow in PowerApps it appears to have this issue. I went into "View" -> "Data" and removed the Flow from there. Then I went back to the button I wanted to call the Flow and clicked to re-add it, it re-registers the Flow with PowerApps. At first, I was getting a "registering error" when trying to re-add it. Did some more digging, what I found is that auto-generating the schema from a payload doesn't always declare all the data types in an array. I had one item in my array that didn't have a declared data type. I manually fixed my scheme to declare the data type, was able to successfully add the flow and this time it came back as expected with all the data rather than just the single boolean.

Thanks @BenStegink. Ran into a similar issue, but your work-around fixed it for me too !

I’ve been struggling with this yesterday again. The problem indeed seems to come from the fact that the metadata from Flow isn’t refreshed properly inside PowerApps. I ended up redoing the Flow from scratch, including the response call BEFORE saving it for PowerApp to read it properly... and it worked.

 

I must say tho, when it works, it’s quite awesome! I’m currently using it in many places, sometimes in conjunction with dynamic SQL queries to support things like complex filtering or paging of large result sets. Next up, I’m going to try to use it for batch updating by sending a collection as a JSON payload through the Proc and use Azure SQL DB’s OPENJSON capability to handle it. Starting to look pretty good ^^

 

@CWesener : maybe as a first step, a simple « refresh » button on the flow panel which would get the latest matadata for the Flow would go a long way ?

Level: Power Up

Thanks @BenStegink. Your post helped me out as well!

Level 10

Hi everyone

 

Below are two videos dealing with advanced queries to SQL from PowerApps (using Flow).
For SQL Azure you can use direct (native) queries:
https://youtu.be/DII10gK715I

 

For on-premesis SQL servers, native queries are not supported from Flow (you will get an error: operation Execute Native Sql is currently not supported using an on-prem gateway connection), so we have to use stored procedures to accomplish the same:
https://youtu.be/BAGBzI4zdww

 

Please let me know if you don't come right.

 

Dawid van Heerden
Follow on Twitter: @davestechtips
Subscribe to YouTube: https://www.youtube.com/davestechtips?sub_confirmation=1
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.

Hey @Dawidvh,

 

Good video!

 

I've had some pretty good success lately doing elegant error handling in try-catch upsert procs using a similar technique. Maybe a video for another time?

 

Ps: Loved the "this is a discussion for another coffee". 😅 That's one I'd take a part in! 

Level 10

Hehe, jip, many more coffees to come 😉

 

Jip thanks @FredericForest , would love to see what you have done.