Using SQL Stored Procedures within PowerApps via PowerFX

Customers can now call stored procedures in Power Automate without incurring performance penalties, leading to improved efficiency in action-based apps.

Feature Details:

• Direct Stored Procedure Calls:  Customers can utilize Power Fx to directly call stored procedures, eliminating the need for additional steps.
• Seamless Integration: Adding a SQL Server Database object to their data sources enables customers to access Stored Procedures actions directly from the database object.
• Performance Optimization: This feature helps customers avoid performance hits during app initialization by calling Power Automate. Additionally, it reduces the need to move data in and out of collections, resulting in better overall data call and update performance.

Benefits:

• Enhanced User Experience: Action-based apps become easier to use with improved paging and refreshing functionalities.
• Streamlined Workflow: Customers can execute stored procedures directly, simplifying the app development process.
• Performance Optimization: Reduced performance penalties and improved data handling lead to enhanced app performance and user satisfaction.

Getting Started

Below is a quick one-minute video on how to quickly get started with SQL Stored Procedures using PowerFx.

The steps are:

• Create SQL Connector within Connections. Ensure you have a SQL Server connector defined within your solution if you do not already, and that it has a custom Stored Procedure.
• Enable SQL Server Stored Procedures within Upcoming Features. This is available from the Canvas Power App settings menu.
• Add SQL as a Data Source within Power App.
  • Click on Data within the Maker app.
  • Add Data, and select your SQL Connector as a source.
  • Enter a SQL Server Name, and Database Name when prompted
  • Add your required Tables and Stored Procedures within Choose a table screen.
    • If the Stored Procedure is to be used in a gallery (i.e. the results are in the form of a collection of records) then select ‘Safe to use for galleries and tables’.
• Now you are ready to use your SQL Stored Procedure. For example, to call a stored procedure you could write: dboTable.spMyStoredProcedure(input.text)

If you have any feedback about this tip, please do not hesitate to drop me a message via my socials.