cancel
Showing results for 
Search instead for 
Did you mean: 

Calling a Stored Procedure in PowerApps

It would be fantastic to have stored procedure access to PowerApps. It's a feature presently blocking a number of customers of ours from implementing PowerApps since much of their logic is in the sprocs already. They don't want to implement Flow for this since they see it as another cog to pay for. This seems like pretty core functionality but there must be some big technical blocker from MS implementing it. 

Status: Under Review
Comments
Level 10

Hi Brian, can you clarify what you mean about their reservations about Flow?

 

I can clarify any possible misunderstanding: Flow is included with all licenses for PowerApps--at no additional cost.

Level 8

Sure @mr-dang by using Flow, they'll be limiitted to the number of calls in the app. 4,500 / month all up in Flow 1's plan for example. If each screen had a sproc like a queue page, it will run through their calls fairly fast. 

Level 10

Hi Brian,

The cited Flow quota is per user--aggregated for the entire tenant. So if you have 10 users with an Office 365 license for PowerApps which lists 2,000 flow runs per user, that would be:

 

2,000 runs * 10 users = 20,000 runs

 

So the tenant would have a quota of 20,000 when you view it in the Admin settings.

 

Let me know what else you would like to clarify.

Level 8

I completely understand. My challenge as a data guy is most if not all of my business logic is written in our sprocs that we use for our other web apps. We'd like to replace some of those web apps with PowerApps so we can uplift the technology. Not have sprocs, will mean a complete rewrite of that layer.

 

 

The customer sees even an aggregated number of calls as a potential barrier of the app stopping all of a sudden. For PowerApps to fully expand its reach, this seems like a must-have for customers would be the full support of SQL basic objects but there might be a high technical barrier I'm missing. 

 

Level 10

Hi Brian,

Having an out of the box way to call a stored procedure from within PowerApps would be an ideal experience. That said, I don't want you, your customers, and your current use cases to be blocked by misconceptions of Flow today:

 

Your flows will not stop working upon reaching the quota. The quota is a soft quota. 

 

We understand the needs of a business continuing to run in spite of little things like run quotas being over used. What CAN happen is that if you do go over runs, your company may receive an email to "true up" and pay for the extended use, or your admin team can pre-purchase additional flow runs at the cost of $40 per 50,000 runs at the tenant level to avoid any of that.

 

This is how the soft quota is implemented today, although we reserve the right to change that in the future. Our goal is to keep everyone running successfully with our platform.

 

From my personal experience as a user of Flow who runs flows for cognitive services, SQL queries, HTTP requests for APIs, and lots of tests, I will say--just do it. 

 

Brian

Level 8

Thanks for the clarification @mr-dang on how quotas are achieved. I'll try to implmeent it that way but ideally in the future, I don't want MS to forget about us DB guys so I'd love to keep this in a backlog idea for future builds. I think it would open another market that you're missing for more lift and shift legacy applications.

 

Thanks again for the clarification Brian! 

PowerApps Staff rc
PowerApps Staff

This is definitely valid to stay on the idea backlog-- upgrading it to under review. We're doing some work now to look at the future of interacting with data sources like SQL direclty from PowerApps, flagging @LanceDelano on that front. 

 

In the meantime, as Mr Dang points out, using Flow to trigger stored procs and return values is a great way to go, and in general that is one of Flow's key roles in the platform -- to orchestrate logic across data sources. Leaving a link to one of Mr Dang's defninitive blogs on the topic for others who pass by here in the meantime: https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

PowerApps Staff rc
PowerApps Staff
Status changed to: Under Review
 
Level 8

Voted. 

 

The Flow connector is not ideal for filling all the gaps, guys - especially since it doesn't easily enable the tabular capabilites.  How about just make an OData connector with tabular capabilities or better yet enable us to create Custom APIs with tabular capabilities.  Then we're not beholden to whether something is supported in the OOB connectors. You know, kill all birds with one stone. Smiley Happy

Level 10

Hi,

I have a powerapps screen that needs to update two separate tables in  a single transaction. Is there an example somewhere on how to do this using a flow that calls a sproc? (of course I would prefer to call the sproc right from powerapps!)

 

Thanks