Calling a SQL Server Stored Procedure from Power Fx

Introduction

Calling a SQL Server stored procedure from Power Fx (the formula language used in Power Apps) is still in preview. I was asked whether it’s possible to call database operations in SQL Server through a Custom Connector or a Power Automate flow. If your Power Apps environment has an established SQL Server connection, you can use the SQL Server connector to simplify the process.

1. Prerequisites

SQL Server Connection

• Go to Data > Add data > Select SQL Server.
• Provide the necessary credentials and connect to the appropriate database.

Stored Procedure Setup

Ensure that the stored procedures already exist in your SQL Server database. Example:

CREATE PROCEDURE GetCustomerById
    @CustomerId INT
AS
BEGIN
    SELECT * FROM Customers WHERE CustomerId = @CustomerId;
END;
            

SQL Permissions

Make sure the SQL Server user has EXECUTE permissions for the stored procedure.

2. Directly Call Stored Procedures

If your SQL Server connector supports it, stored procedures will appear as available actions. Verify that the stored procedures are properly configured in your database.

3. Using Power Automate as a Bridge

Create a Flow

• Open Power Automate and create a new flow.
• Add the SQL Server connector and choose the Execute stored procedure action.
• Specify the procedure name and required parameters.

Test the Flow

Run the flow with sample inputs to ensure the stored procedure executes correctly.

Expose the Flow in Power Apps

• Use the Power Apps trigger in the flow to make it callable from Power Apps.
• Save and publish the flow.

4. Integrate the Flow in Power Fx

Add the Flow to Power Apps

• Go to Data > Add Data.
• Search for and add the flow you just created.

Call the Flow Using Power Fx

Use this formula to trigger the flow:

Set(ResultVariable, YourFlowName.Run(Parameter1, Parameter2))
            

Replace YourFlowName with the name of your flow and pass the necessary parameters.

Handle Returned Data

If the flow returns data, you can store and display it:

ClearCollect(ResultsCollection, YourFlowName.Run(Parameter1, Parameter2))
            

Bind ResultsCollection to UI components like galleries or tables.

If the procedure only performs an action:

Notify("Procedure executed successfully!", NotificationType.Success)
            

5. Display or Process Data in Power Apps

Use collections to bind returned data to UI elements. For action-based procedures, show success notifications to the user.

Additional Notes

• Custom Connector Option: For complex scenarios, consider creating a Custom Connector in Power Apps to directly execute SQL operations, including stored procedures.
• Error Handling: Implement error handling in Power Automate to manage exceptions during stored procedure execution.
• Security: Ensure your Power Apps/Power Automate plan supports the SQL Server connector and stored procedure execution.

Limitations to Consider

• Connector Dependency: Directly calling stored procedures depends on the SQL Server connector’s capabilities.
• Plan Requirements: Ensure your Power Apps licensing plan supports premium connectors like SQL Server.
• Security: Follow best practices to secure your database and use least-privilege access to prevent data exposure.

© 2024 SQL Server Integration Guide. All Rights Reserved.