This article will walk you through the process of creating a button in Power Apps that runs a Flow which executes a SQL Stored Procedure.
Step 1: Create a stored procedure
Open SQL Server Management Studio and navigate to your database.
Open the Programmability folder
Right click on Stored Procedures and select New
4. Copy, paste the following query into the query window then click
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE RandomNumberGenerator
AS
SELECT FLOOR(RAND()*(1000-1+1))+1 AS RandomNumber
GO
Step 2: Create a blank Canvas Power App
1. Open a browser and navigate to make.powerapps.com and login
2. Click Create on the right-hand side and then click Canvas app from blank
3. Give your app a name, choose either mobile or tablet, then click Create
4. Click Insert from the top menu and insert both a Button and a Label from the Text drop down.
5. Change the text of the button to say “Generate Random Number”
6. Change the text of the label to say “Random Number: “
7. Be sure to save your app by either navigating to File > Save or press Ctrl+S
Step 3: Create a flow to run your stored procedure
8. Next click on the button you created, then click Action from the top menu bar followed by clicking Power Automate.
9. From the right-hand side Data menu click “Create a new flow”
10. This will redirect you to the Power Automate page.
11. From the right-hand side click Create and then “Automated flow” under “Start from blank”
12. Click Skip on the popup asking for a name and trigger.
13. Once you are in the flow select PowerApps from the Triggers list.
14. Select + New Step
15. Search for Execute stored procedure for SQL Server
16. Put in your server name, database name, and the procedure name which is dbo].[RandomNumberGenerator].
17. Select + New Step and search for Initialize Variable
18. Give the Variable a name, Type as Array and the Value as “ResultSets Table1”
19. Select + New Step and search for “Respond to a PowerApp or flow”
20. Add a text output
21. Name the output “RandomNumber” and for the value enter the name of the variable you just created.
22. In the top left, name your flow “RandomNumberGenerator” then click save.
Step 4: Putting it all together
23. Navigate back to your Power App
24. Once again click on your button and navigate to Action > Power Automate, then click the name of the flow you just created.
25. Change the OnSelect of your button to: Set(VarRandNum, 'PowerApp->Executestoredprocedure(V2)'.Run())
26. Next change the text of your label to: "Random Number: " & First(Split(Last(Split(VarRandNum.randomnumber, ":")).Result, ".")).Result
27. Finally click the Play button in the top right and push your button and watch your label change.
This is a very simple example of what can be done using Flows and Stored Procedures. Now that you know some of the basics see what else you can do using flows and stored procedures!
Contact JourneyTEAM for more Power Apps guidance or questions.