Executing SQL Server stored procedures with Power FX in Power Apps

Microsoft has introduced a new feature that enables direct calling of SQL stored procedures in Canvas Apps using Power FX. Previously, invoking stored procedures from Power Apps required the use of Power Automate flows, which added complexity and performance overhead.

Prerequisites:

• Power Apps: Microsoft Dataverse access with permission to create apps.
• SQL Stored Procedure: An existing SQL stored procedure that you want to call/execute in Power Apps.

Note: A data gateway is required when connecting to an on-premises SQL Server.

Let’s explore how to call SQL Server stored procedures in Canvas apps:

Step 1: Sign in to Microsoft Dataverse i.e. https://make.powerapps.com

• Create a Canvas app.
• Go to Data > Add data > Select SQL Server and provide all required details.

• After connecting, go to the Stored Procedures tab and select the stored procedure you wish to execute.

When you select a stored procedure, an option appears as a child node, enabling you to designate the procedure as safe for integration within galleries and tables. Enabling this option allows Power Apps to treat the procedure as an Items property, meaning it will be invoked whenever the control refreshes.

Important Considerations:

• No Side Effects: Ensure that the procedure does not have adverse effects when called multiple times.
• Modest Data Volume: The stored procedure should return a manageable amount of data (ideally fewer than 2,000 records) to prevent performance issues, as it will not be automatically paged like other data sources.

Calling the Stored Procedure with Power FX:

• You can call the stored procedure with Power FX using the following formula:

Table( D365.dboGetCompanyAllDetails().ResultSets.Table1)...Read More