Hi Folks
My group is familiar with calling stored procedures from Power Automate, where the generalized scenario is that you are returning a result set (rows).
This scenario is different, the underlying stored procedure takes in the SINGLE row to be updated. The row fields are both INPUT AND OUTPUT parameters + plus a RETURN CODE (giving you status on the update). So we aren't really getting a row set, we are getting a refresh of the same record (if successful) and always a return code.
Objectives:
1. use the same stored procedure we'd use with any other technology (.NET, Java, MS Office, etc.)
2. use real-world optimistic concurrency => RowVersioning (because .Net and Java, have their own persistence mechanisms, like Entity Framework and Hibernate, that Power Automate does NOT)
3. use real-world update checking like hash-bytes, to know that values have actually changed (versus wasting an update needlessly)
4. use Power BI Canvas Apps like we would use any other form-like technology and choreograph updates with all of the other form and non-form (batch jobs) technologies we already have in our enterprise.
So we have all of your 101 examples, for demonstrating optimistic concurrency (.Net, Java, MS Office, Enterprise Automations, etc.), except via the Power Suite. They all work and play well, so we can hand them off to folks to learn, pattern from, understand best practices, regardless of technology framework. They all walk, quack, etc. the same and play well.
The dilemma is to understand how to get back that same row/fields and return code (using the stored procedure updated standards we have in place, and without changes and/or minimal changes).
Screen shot here, to better help with the understanding the Canvas App (with Power Automate behind it).
Power BI reference for Optimistic Concurrency
