How to run a SQL Server stored procedure in PowerApps

Question

I would like to call a SQL Server stored procedure called get_customer from my PowerApps flow. How do you do this?

I can see in posts that others have been able to do this but I cannot see the option in PowerApps and the only data source options I have is to access the tables and views and not the stored procedure.

I have a button on screen 1 and on click I want it to call a stored procedure and then display the results in a list on the same form. How can I do this?

Thanks

Fipple

Link - powerusers.microsoft.com/t5/General-Discussion/… – Squier

power4apps.wordpress.com/2019/01/14/… – Goner

Answer 1

If I understand correctly:

• You want to call a SQL Server stored procedure called get_customer
• You do not see the option in PowerApps
• You have a button on Screen1 you want to click to call the sproc and return its result

The method I have been calls the stored procedure through Flow. Here's a general idea of how to construct this flow:

1. Insert the PowerApps trigger as the first step in your flow
2. Search for SQL and select the action for 'Execute a SQL stored procedure'
3. Select get_customer from the dropdown menu for SQL stored procedures
4. Test out the flow so you can get sample data. View its results.
5. In the output of the SQL action, copy the sample data from square bracket [ to square bracket ] to use for generating JSON schema in the next step
6. Edit the flow again
7. Search for and insert the 'Request - Response' action
8. Click 'Use sample payload to generate scheme' and paste the sample data
9. Configure the body of the Request Response step as: body('Execute_a_SQL_stored_procedure').ResultSets.Table1 The naming in this step may vary depending on your setup.
10. Name your flow, then same it.

11. In PowerApps, select your button. You can bind it to the flow you just created:

    Action > Flows > select your flow

12. Collect the results of your flow to a collection.

13. Test out your button. View your Collections to see how it did

Here is a more precise blog on the related topic for executing a general query, but it applies to your question: https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

Here is a video demonstrating the flow and the Response action in multiple contexts: https://www.youtube.com/watch?v=MoifwDFKSTo

Please let me know if this has helped.

Mr. Dang

Rusell

Well described and exactly what I needed to solve an issue! – Leyla