SQL & Stored Procedures in Canvas Apps

Using SQL in PowerApps

The great thing about PowerApps is the possibility of connecting to various data sources and services — see the full list of connectors. In most cases, people could choose between Dataverse, SQL and SharePoint. In this article, let’s focus on SQL and describe the 2 most useful elements.

Views

The SQL database allows you to create Views. Canvas App treats them like separate data tables that can be connected and added as a data source to your elements, for example, Gallery, Table, and Form. Operations like Group By, Filter By, and JOIN can be done on the client side, but it will make your app heavier. All the mentioned operations can be moved to the SQL database View object. You can create dedicated views for galleries with required columns, so you don’t need any code to retrieve data/information from the related table in your app. It is very beneficial, especially in the case where you need to perform complex query with multiple JOINs. This is way more difficult to perform with other data sources.

Stored Procedures

SQL code that you can save and reuse multiple times is called a Stored Procedure. An important fact is that you can pass parameters to it. Why it’s important? You can move a load caused by e.g. calculations from your app to a server, so you can keep your app lightweight and perform complicated queries on a server.

You can create a Power Automate flow and use an action called “Execute stored procedure (V2)”

Unfortunately, when you go throughhttps://docs.microsoft.com/en-us/connectors/sql/ and scroll to the section called “General known issues and limitations”, you can find a point that states “If the SQL query/stored procedure execution time exceeds 110 seconds, actions will timeout.“ That might present a significant problem when the user waits for a result, and the procedure takes longer than 110 seconds to complete. Luckily, there is a way to use stored procedures and inform users about the results!

Stored Procedure Workaround

There are many ways to perform this workaround but, in this article we will focus on the method that was actually used in one of our projects.
In your canvas app, you trigger power to automate flow add pass e.g. RecordID. The flow contains actions:

• generate GUID
• insert a row to the Logs table with information that the Flow for RecordId has been started and associate it with GUID
• put a message on the queue

• insert a row to the Logs table with information that the Flow for RecordId has been finished and associate it with GUID
• return GUID to our Canvas App

The Azure-side message is picked up by Azure Function (we were already using it in our project) and then triggers the stored procedure. At the very end of the procedure, we added a row to our Logs table with the information that the procedure is finished.

Once the Canvas App receives the GUID, it stores its value in a variable and triggers Timer Control.

In Timer Control, you can act every defined period, so you can set the timer so it will be refreshing the Logs table every 10 seconds. In every loop, you’ll check whether the stored procedure returned the answer. If so you, can notify a user that our calculation has been finished and turn off the timer.

Timer Property – OnTimerStart:

Summary

To summarise, leveraging SQL as a Datasource for your CanvasApp is a very powerful tool, and using Views can simplify formulas used in your app. However, you need to be aware of its limitations as you deliver high-quality apps to your clients and users.