How to run a SQL Server stored procedure in PowerApps

sql-server stored-procedures powerapps
user3165854 picture user3165854 · May 21, 2018 · Viewed 13.4k times · Source

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

Answer

Mr. Dang picture Mr. Dang · May 21, 2018

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

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More