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 8

@RussellGove, I agree with you there of course since I'm a data guy at heart. I'm doing a live video in about 45 minutes here (9amEST) to walk through how to do this with the assistance of Flow: https://www.youtube.com/watch?v=qb9Sloxw8wc. There will be a recording in case you miss it at the same link.

 

I realize it's not perfect but it's the only way at this time outside of a custom connector. 

Level 8

Another option, for DB guys, to avoid flow it to use what we in LightSwitch called the 'Command Table Pattern'.

 

Basically you create a table in SQL Server database that accepts 'command' records.  Then you create an AFTER INSERT TRIGGER in SQL on this table which executes sprocs server-side. 

 

PowerApps will add a record in this table and the sprocs execute on the server and update this table with result.

 

This is easy for spocs that just execute an 'action' on the server without a bunch of input variables/fields, but can also be used to pass data with more config.

 

HTH,

Josh

Level 8

Thanks @BrianKnight, greate viedeo.! So with that IsError thing you spoke about can i pass an error from the sproc, through the flow, and back to the powerapp?

Level 8

 Hi @RussellGove I'm afraid I misspoke in the video. It's actually IfError. I'll work on an example today for that and post soon. It's still experimental and tempermental. Smiley Happy

Level: Powered On

Thanks fot posting this important idea Brian.

Level: Powered On

I would love to be able to called Stored Procedures directly from PowerApps. I prefer to have all my code in the same place and using Flows for this seems both convoluted and unmanageble. It's just gonna evolve into another list of code-snippets that has to be managed and maintained.

Hopefully this is not derailing the topic too much but @joshbooker , could you elaborate on your Command Table Pattern and how you implemented it? I'm specifically having an issue with the use of Triggers on tables that PowerApps wants to insert into. I'm getting the error 

The target table 'dbo.xxxx' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Which is discussed in the following post. If you actually bypassed this problem I would love to hear your solution!

But on topic, please implement calling stored procedures directly from powerapps ASAP. It would provide a good implementation for many issues I have with PowerApps (such as Transactionality over inserts into multiple tables)

Any update on this ? @rc 

 

I'm totally with the group here that this would be a killer featuer. The ability to simply have a MyConnectionName.MyProc(param1,param2) would simplify apps significantly and make for a great experience.

 

I personally end up having 5-10 flows per app which, beside being clunky, add to the maintenance since we need to keep the HTTP response in-sync in the flow every time we change the procs.

Level: Powered On

The ability to call sprocs and table valued functions from powerapps would really simplify arechitecture, enable a whole lot of use-cases where simple table selects are insufficient and make the whole life-cycle less error prone.

Having code spread across powerapps, flows, and SQL backend is neither elegant nor wise in terms of support and maintenance.

 

Example usecase, if I need to find questions that a user hasn't yet answered, I need to be able to launch a query with join condition based on user, anti-semi-join, or except statement. All of these are currenltly inaccessible. Let me call a simple sql function that accepts a parameter. The function return schema is pre-defined, thus shouldn't be too difficult to implement technically.

Level 8

@vverb

 

huh. To be honest, i haven’t tried the trigger pattern with PA. I suggested the pattern with the assumption PA wouldn’t fail when a trigger exists. What a major fail this is. 

 

It’s evident that the team needs more resources on iterating long standing user requests for connector improvements such as this one.

 

What we we really need is the ability to overcome this lack of advancement with the ability do do it ourselves with custom connectors. 

 

 

Please vote if if you agree:

Custom API with tabular capabilities 

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Custom-API-with-tabular-capabilities/idi-p/13394

 

OData connector with tabular capabilities 

https://powerusers.microsoft.com/t5/PowerApps-Ideas/OData-connector-with-tabular-capabilities/idi-p/...

 

Thanks for listening

 

Level: Powered On

@joshbooker thank you for elaborating. I voted for your idea even though i haven't played around with custom connectors yet. Any increase in custom programmability is a good idea for PowerApps. It already shines as a tool for developing small simple apps but i keep hitting roadblocks or dissapointments when I try to make the apps more professional, maintainable and more complex. Maintaining numerous flow for every stored procedure reallly complicates the setup and process for a good Development > Production release cycle and makes it quite error prone, along with all the other points raised before here. Not being able to use triggers closes another door for elegant transactional crud operations.

The speed with which new updates and improvements are made to the PowerApps tool is impressive, but when it comes to these kind of more "techy" less "drag and drop development" focussed ideas i always find 2-3 year old topics of deafening silence on Microsoft's part. That worries me. The easy nature of getting started with Powerapps is great, but if it ends up being a gateway drug to dissapointment and wasted potential/money/time... The team really needs to show that they care about features required for mature SQL based apps, imho. 

 

Also bump