[Preview] Directly Calling SQL Server Stored Procedures in Power Apps: A Step-by-Step Guide

1. Introduction
2. What are Stored Procedures?
3. Why Use Stored Procedures?
4. Why Use Direct Calls?
5. Pre-requisites
6. Step-by-Step Guide
   • 1. Enable the Preview Feature
   • 2. Create a New SQL Connection
   • 3. Call the Stored Procedure in Power Fx
   • 4. Displaying Results
7. Example in action
8. Common Issues and Troubleshooting
9. Benefits and Considerations
10. Embrace the Future of Power Apps & SQL Server Integration

Introduction

Hello Power Addicts! We know that working with SQL Server stored procedures has traditionally involved Power Automate flows. This added an extra layer of complexity and slowed down development. A powerful new feature in Power Fx lets you directly call stored procedures, streamlining your workflow and boosting app performance.

But before we go further, let’s quickly remember what SQL Server stored procedures are.

What are Stored Procedures?

Stored procedures are pre-written chunks of SQL code stored in your database that can be executed on demand to perform specific tasks or operations on the database.

Why Use Stored Procedures?

Stored procedures in SQL Server offer several advantages. Some of the worth mentioning are:

1. Security: Stored procedures can help enforce security measures by allowing controlled access to the database. Users can interact with the database through predefined procedures, reducing the risk of SQL injection attacks.
2. Performance: Stored procedures are precompiled and optimized, leading to improved performance compared to dynamic SQL queries.
3. Modularity: By encapsulating database operations within stored procedures, you can promote code reuse and maintainability.

Why Use Direct Calls?

Previously, calling stored procedures from Power Apps required a Power Automate flow. This meant:

• Increased complexity: Building and managing flows added an extra layer of development.
• Performance overhead: Data transfer between Power Apps and Power Automate introduced delays.

Direct calls eliminate these hurdles:

• Simplified development: Write code directly in Power Fx, reducing complexity and development time.
• Enhanced performance: Enjoy faster data interactions between Power Apps and your SQL Server.
• Improved maintainability: Keep logic centralized within stored procedures for easier code management.

Getting Started

Pre-requisites

Before we begin, make sure you have the following prerequisites:

• PowerApps: Access to Microsoft PowerApps and sufficient permissions to create apps.
• SQL Server: A SQL Server instance with a database containing the desired stored procedures.
  For this example, I am going to use an Azure SQL Server instance and I will use SQL Server Login to authenticate.
  Note: If you are using on-premise SQL Server, please make sure to complete the necessary confguration including setting up of a gateway to access the SQL Server instance.

• Stored Procedures: Existing stored procedures in your SQL Server database that you want to call from PowerApps.
  I will be using two stored procedures:
  i. [dbo].[usp_GetCustomer]) – it has no parameters

CREATE PROCEDURE [dbo].[usp_GetCustomer]
AS
BEGIN
  SELECT * FROM [SalesLT].[Customer];
END

ii. [dbo].[uspGetCustomerDetails] – it accepts one parameter: CustomerID

CREATE PROCEDURE [dbo].[uspGetCustomerDetails] @CustomerID INT 
AS 
SELECT * 
FROM [SalesLT].[Customer] 
WHERE CustomerID = @CustomerID
GO

• Connector: Ensure your SQL Server database is accessible from PowerApps using appropriate connectors.
  To achieve this for an Azure SQL Server instance, navigate to Networking and add your client IPv4 address and select the checkbox to Allow Azure Services and resources to access this server and Save the changes.

Note: If you don’t enable this setting, you’ll see the following error message when you attempt to connect to your database in PowerApps.

If you go to the database in Azure Portal and try to create a PowerApps, you’ll also encounter the same error there.

Step-by-Step Guide

Important Disclaimer: This functionality is currently in preview. Preview features are not guaranteed to be released in a generally available version and may be subject to significant changes. Use caution when implementing preview features in production environments.

Let’s dive into how you can call SQL Server stored procedures from PowerApps.

1. Enable the Preview Feature

• This functionality is currently in preview. To activate it:
  • Open your Power App.
  • Navigate to Settings > Upcoming features > Preview.
  • Toggle SQL Server stored procedures to On.

2. Create a New SQL Connection

• Go to Data > Connections > New connection.
• Select SQL Server and provide your connection details.

• Once connected, choose the Stored Procedures tab in the table selector.
• Select the stored procedures you want to access in your app.

Once you select a stored procedure, a child node appears and you can designate the stored procedure as Safe to use for galleries and tables. If you check this option, you can assign your stored procedure as an Items property for galleries for tables to use in your app.

Enable this option only if:

1. There are no side effects to calling this procedure on demand, multiple times, whenever Power Apps refreshes the control. When used with an Items property of a gallery or table, Power Apps calls the stored procedure whenever the system determines a refresh is needed. You can’t control when the stored procedure is called.
2. The amount of data you return in the stored procedure is modest. Action calls, such as stored procedures, do not have a limit on the number of rows retrieved. They aren’t automatically paged in 100 record increments like tabular data sources such as tables or views. So, if the stored procedure returns too much data (many thousands of records) then your app might slow down or crash. For performance reasons you should bring in less than 2,000 records.
   (More details on Microsoft Learn)

3. Call the Stored Procedure in Power Fx

YourConnectionName.YourStoredProcedureName(Parameter1, Parameter2, ...)

• Replace YourConnectionName with the name of your SQL connection. In my example, it is azsqldb1
• Replace YourStoredProcedureName with the actual name of your stored procedure. in my example they are dbouspGetCustomer and dbouspGetCustomerDetails.
• Include any required parameters for the stored procedure within the parentheses.

4. Displaying Results

• I used the Sidebar auto layout template and added a gallery on the sidebar in my canvas app. In the main content area, I placed a form control. The gallery will show a list of customers using the dbouspGetCustomer stored procedure. The form will display the details of each selected customer, retrieved by calling the stored procedure dbouspGetCustomerDetails.

• In the gallery items property, I will write the below code which will call the stored procedure dbouspGetCustomer.
  azsqldb1.dbouspGetCustomer().ResultSets.Table1

Note: When you have a gallery with records, you can’t use the Refresh() function with stored procedures. You can work around this limitation by using a variable in the OnVisible property for the screen and setting the stored procedure to the variable. (More details in Microsoft learn)

• On the OnSelect property of the right arrow icon, I will write the below code which will set a new variable named colCustDetails to fetch the details of the selected customer through the stored procedure dbouspGetCustomerDetails.
  Set(colCustDetails, azsqldb1_1.dbouspGetCustomerDetails({CustomerID:ThisItem.CustomerID}).ResultSets.Table1)

• Set the Data source property of the form to Customer table.
• Set the Item property of the form to First(colCustDetails).

We have now successfully completed the entire process of implementing direct calls to stored procedures and displaying the results.

Example in action

Common Issues and Troubleshooting

• SQL Server connector issue in PowerApps
• Azure SQL DB configuration error
• Refresh() not working with galleries

Benefits and Considerations

Direct calls offer numerous advantages, but keep these points in mind:

• Security: Ensure proper access controls are in place for both Power Apps and the stored procedures themselves.
• Complexity: While simpler than Power Automate flows, complex stored procedures might still require additional logic within Power Apps.
• Preview Feature: As mentioned earlier, this functionality is in preview. It may have limitations or bugs, and its future availability is not guaranteed. Thorough testing is crucial before deploying in production environments.

Embrace the Future of Power Apps & SQL Server Integration

Direct calls for stored procedures mark a significant step forward for Power Apps developers. This powerful feature simplifies development, enhances performance, and unlocks new possibilities for data manipulation within your Power Apps. So, dive in, leverage stored procedures with caution, and create even more efficient and robust Power Apps!
Happy low coding!

Check James Yumnam’s original post https://jamesyumnam.com/2024/04/16/preview-directly-calling-sql-server-stored-procedures-in-power-apps-a-step-by-step-guide/ on jamesyumnam.com which was published 2024-04-16 18:51:00