Executing SQL Server stored procedures with Power FX in Power Apps

A stored procedure in SQL is a pre-defined collection of SQL commands stored within the database, optimized to enhance execution efficiency and support consistent reuse across applications. It can include various operations such as SELECT, INSERT, UPDATE, or even complex logic involving loops and conditionals. Stored procedures are essential for modularizing code, enhancing performance, and providing better security in database management systems.

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)

D365 is the name of the SQL Server database.
dboGetCompanyAllDetails() is the stored procedure being called, with any required parameters passed within parentheses.
ResultSets.Table1 refers to the result of the query. When multiple result sets are returned, they are typically organized as tables (e.g., Table1, Table2, etc.). This indicates that the stored procedure can return multiple tables, with Table1 representing the first result set in the output.

To display the results in a gallery in Canvas apps, use the above formula in the Gallery’s Items property:

You can also store the results in a collection for reuse elsewhere:

ClearCollect(CompanyData, Table(D365.dboGetCompanyAllDetails().ResultSets.Table1))

Conclusion:

With these steps, SQL stored procedures can be efficiently executed in your Canvas apps using Power FX, streamlining the app development process and enhancing overall performance.