Power Community

Power Community

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

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

SQL Server stored procedures

What are SQL Server Stored Procedures?

The above mentioned feature links to Make direct calls to SQL Server stored procedures on Microsoft Learn.

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

Maybe 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:

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

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

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.

New in Power Apps! How to Run SQL Server stored procedures without Power Automate in 2024 Microsoft Power Apps, Microsoft SQL Server image 35

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

New in Power Apps! How to Run SQL Server stored procedures without Power Automate in 2024 Microsoft Power Apps, Microsoft SQL Server w8iNJOroFU7jAAAAABJRU5ErkJggg==

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

New in Power Apps! How to Run SQL Server stored procedures without Power Automate in 2024 Microsoft Power Apps, Microsoft SQL Server image 36

First make sure that you have enabled the preview feature.

New in Power Apps! How to Run SQL Server stored procedures without Power Automate in 2024 Microsoft Power Apps, Microsoft SQL Server image 44

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

New in Power Apps! How to Run SQL Server stored procedures without Power Automate in 2024 Microsoft Power Apps, Microsoft SQL Server image 38

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

New in Power Apps! How to Run SQL Server stored procedures without Power Automate in 2024 Microsoft Power Apps, Microsoft SQL Server image 39

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

New in Power Apps! How to Run SQL Server stored procedures without Power Automate in 2024 Microsoft Power Apps, Microsoft SQL Server image 40

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.

New in Power Apps! How to Run SQL Server stored procedures without Power Automate in 2024 Microsoft Power Apps, Microsoft SQL Server image 41

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.

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

New in Power Apps! How to Run SQL Server stored procedures without Power Automate in 2024 Microsoft Power Apps, Microsoft SQL Server image 43

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

5 Benefits of In-App Notifications for Microsoft Dynamics 365 CRM users

For a successful sales process, you need to stay up-to-date with crucial sales information like deal closures, opportunities won,...

More Articles Like This

- Advertisement -spot_img