How to Run SQL Server stored procedures in Power Apps without Power Automate in 2024

Recently Microsoft released a new feature in the ultimate Power Apps user guide that will let you call SQL Stored procedures without needing to call a flow in Power Automate

What are SQL Server Stored Procedures?

A Stored Procedure is nothing more than a piece of code that will do something within your database. Well that is a great description!

The above mentioned feature links to Make direct calls to SQL Server stored procedures on Microsoft Learn. This article however isn’t much help if you want to get started with making your Power Apps solutions communicate faster with your data.

Maybe we should look at an example. Imagine that we have a table with cars and we want to select all cars that have a specific colour. The following procedure would do this give us an option to specify a colour and the required car records would be returned.

CREATE PROCEDURE PROC_GetColourCars
(
    -- Add the parameters for the stored procedure here
    @SelectedColour nvarchar(256) = NULL    
)
AS
BEGIN
    
    SELECT *
       FROM Cars
       WHERE Colour = @SelectedColour
END
GO

Typically we would want to ask the user of an app to supply the colour and then get the stored procedure to return the relevant records to us.

Why would you use SQL Server Stored Procedures?

There are a few of reasons but the main reasons will be

• Performance
• Hiding complexity from your app
• Reliability and efficiency of the app

The above example was simple of course, but how about if we wanted to read data form multiple tables or if we wanted to update specific records or if we wanted to do anything else that we could easily do with SQL.

Dataverse or SQL Server

Of course, from a Power Platform perspective I would always prefer to use SQL Server, but if you have data that lives in SQL Server, why would you want to copy that between two different locations. You might as well access data where it currently resides rather than moving it all the time to ensure that two databases are always up to date.

To use SQL Server connections in Power Apps, we just add a connection to our app and now we can use data from the table that we selected.

But how do we use Stored Procedures?

Creating SQL Server Stored procedures connections in Power Apps

So how can add a stored procedure as a datasource?

Like with the tables we select SQL Server

Then you can select either a Dataset or create a new dataset:

First make sure that you have enabled the preview feature.

Now when you have enabled the preview feature to call SQL Server stored Procedures you will notice the Stored procedure tab:

In the Stored Procedures we will find our stored procedure that we created earlier in out SQL Server Database.

Once we select the stored procedure we have to make an additional choice. Is this Stored Procedure safe to use in galleries and tables?

So for example, if you used this stored procedure and it does updates to tables, would you want this procedure to run within a gallery? Probably not. Unless of course you wanted to audit users accessing data.

Ok, now we have a connection called after my database. Hmm, this could be confusing. WE better have a look at how this is going to work. Maybe Power Apps is going to surprise us. It looks like the connection asks us for one or more stored procedures. And we can access it through as database object. That looks quite nice and clean!

Calling SQL Server Stored procedures in Power Apps

I created a little demo app. (Yes, it doesn’t look good and wouldn’t pass my QA tests). On the top left I can add cars. On the right I’m listing all cars in my table.

In the Items property of my gallery, displaying the Green cars, I only have to use the following line of code.

sqldemo.dboPROCGetColourCars({SelectedColour: TextInput2.Text}).ResultSets.Table1

At the bottom left I can filter by colour. And my gallery will show just the Green cars.

So now we have the option to populate galleries using data returned by the very efficient stored procedures in SQL Server. This will reducing the complexity of your app and make the data traffic between your app and your database more efficient.

Limitations

So far one of the limitation that I have found is that stored procedure cannot be called using the Mobile Power Apps app. Running a stored procedure on a mobile app results in “Resource not found” error messages.

Another thing to be aware of is that when you add additional stored procedures to your app, from the same database, you might want to remove the existing connection first. Otherwise you might end up with multiple connections as shown below.