Using Stored Procedures directly in Power Apps

As of March 9, 2024, Microsoft has released the ability to call SQL Stored Procedures directly from within a Power App. This is a massive improvement for anyone using Power Apps with SQL! Previously, if we wanted to use stored procedures, our app would need to execute a Power Automate Flow, which would call the stored procedure, then we would return the Response to the Power App. This was tedious, slow, and required a premium license.

This feature is in Preview and should be Generally Available in the coming months. It’s not recommended to use this for production apps at this time. However, this is definitely a feature you’ll want to implement if you use stored procedures in your solutions.

Enable the feature

• Edit your Power App.
• Open Settings.
• Under Upcoming Features, enable SQL Server stored procedures.

Add your stored procedure

• Edit your Power App.
• Open Data.
• Click Add Data.
• Select SQL Server.
• Select your SQL Server Connection.
• A panel will open on the right. Select the Stored Procedures tab.
• Find your stored procedure(s) and select them.
• Click Connect to add them to your app.

Calling your Stored Procedure

Calling your stored procedure is pretty simple. In your formula, start typing the name of your stored procedure. The database name and stored procedure name will appear.

If your stored procedure has any input parameters, you’ll need to include them. You will also want to save your results to a variable or collection so that you can use it in your app.

UpdateContext(
{
    tmpResult: DBName.dboSaveItems(
    {
        MyCustomItems: JSON(colMyCustomItems),
        MyCustomDetails:JSON(colMyCustomDetails)
    }
    ).ResultSets
}
);

In my example, DBName is the fake name of my database, dboSaveItems is the fake name of my stored procedure. My stored procedure takes two input parameters, MyCustomItems, and MyCustomDetails. These parameters are expecting JSON. Long story short, I’m taking the data in my app, patching it to a collection, then converting it to JSON and passing it to the Stored Procedure. The Stored Procedure parses this JSON, saves all the data, and returns the results. All of this is run in a Transaction, which means if there are any errors, everything is rolled back.

The main point is, I’m calling my procedure and passing in my input parameters. The stored procedure then returns the results. In my current production apps, we do the same thing, except we pass this JSON to a Power Automate Flow, which calls the stored procedure, then returns the results to the app.

Reading your results.

This is where things can get tricky.

Your results are returned as an Untyped Object. Power Apps can handle this data type, but not as elegantly as other types. In short, you have no IntelliSense that would normally make it easy to use your results simply by typing the name of the properties you want to reference.

Your stored procedure may return a wide variety of things, from single output parameters, to entire tables of data.

You need to know what the results look like to read the values. To achieve this, open your Monitor tool, then execute your Stored Procedure. Look at the results of this execution in the Monitor Tool.

Here are the results from this example stored procedure, which returns a single text value containing the new ID of the item that was created.

"body": {
    "ResultSets": {
        "Table1": [
            {
                "ItemId": 24567
            }
        ]
    },
    "OutputParameters": {}
}

To fetch the value of my ItemId column from tmpResult, I’d need to write a formula that finds the specific column and converts the value into whatever DataType it is.

Text(Index(Table(tmpResult.Table1), 1 ).Value.ItemId )

The above example converts Table1 into a Table object, then gets the first row of the table using the Index function. Finally, we get the value and convert the ItemId column to text.

If my column Data Type were a number, I would use Value() instead of Text(). If it were a date, I’d use DateValue(), and so on.

The result of my formula is 24567.

What if I want to return a collection of data from my stored procedure?
In this scenario, my stored procedure returns a collection of data that I intend to use in my app. Calling the stored procedure is essentially the same.

I’ll add a button to my page that will be used to execute the stored procedure and return the results into a collection that I can use elsewhere in my app, perhaps a Gallery, Table, or a Combo box control.

The first part of my formula triggers the stored procedure and returns the results.

UpdateContext({tmpItemStatusValues: DBName.dboGetItemStatusTypes().ResultSets});

Again, execute your stored procedure while using the Monitor Tool. Read the results. You should see something that resembles the following, only with your data.

"body": {
    "ResultSets": {
        "Table1": [
            {
                "Id": "6F5A10C4-5D6B-4091-A439-45D4DC141A99",
                "WhenCreated": "2022-09-30T15:28:41.2066667",
                "WhenModified": "2023-01-05T13:46:56.56",
                "Name": "Pending",
                "Description": "This item is pending",
                "SortOrder": 1,
                "IsActive": true
            },
            {
                "Id": "7B21644F-C4D1-45DD-BAA1-17533C64895B",
                "WhenCreated": "2022-09-30T15:28:41.2066667",
                "WhenModified": "2023-01-05T13:46:56.61",
                "Name": "Complete",
                "Description": "This item is complete",
                "SortOrder": 2,
                "IsActive": true
            }
        ]
    },

The important part is the structure itself. You have Table1 returned as a nested node under ResultSet. Since I’m saving ResultSet to my variable, tmpItemStatusValues, I now need to write a formula to read it and convert it into something usable.

In some controls, like the Gallery, we can simply add our Table1 directly into the control Data Source and start using it. Unfortunately, if we want to use it elsewhere as a Typed object (with IntelliSense) we’ll need to convert the results.

Next, update your button’s OnSelect property to include the For All ForAll function. We’re looping through, our returned data and converting each field into a usable Data Type that we can use in the app. The results are saved to a new collection named colItemStatusValues.

UpdateContext({tmpItemStatusValues: DBName.dboGetItemStatusTypes().ResultSets});

ClearCollect(
    colItemStatusValues,
    ForAll(
        tmpItemStatusValues.Table1,
        {
            Name: Text(ThisRecord.Name),
            Description: Text(ThisRecord.Description),
            Id: GUID(ThisRecord.Id)
        }
    )
)

We can now use the colItemStatusValues collection as we do any other collection in our app!

Conclusion

I’m certain there are other ways to handle this, and I’m learning them as I go. As a quick recap, I demonstrated how to execute a stored procedure directly from a Power App and convert the result into something we can use in our app. This bypasses the need to use Power Automate to call stored procedures, which makes things faster and more efficient.

---

---