Using Stored Procedures in Power Apps

As of March 9, 2024, Microsoft has enabled calling SQL Stored Procedures directly from a Power App. This is a significant improvement! Previously, our app needed to execute a Power Automate Flow to call the stored procedure and return the response to the Power App. This was tedious, slow, and required a premium license.

Enable the feature.

• Edit your Power App.
• Open Settings.
• Enable SQL Server stored procedures under New Features.

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. Then, select the Stored Procedures tab.
• Find your stored procedure(s).
• Click Connect to add them to your app.

Calling your Stored Procedure

Calling your stored procedure is straightforward. Start typing the stored procedure name in your formula, and the database and stored procedure name will appear.

If your stored procedure has input parameters, include them. Save your results to a variable or collection for your app.

DBName.dboSaveItems(
    {
        MyCustomItems: JSON(colMyCustomItems),
        MyCustomDetails:JSON(colMyCustomDetails)
    }
    ).ResultSets.Table1

Reading your results.

This is where things can become complicated.

Your results are returned as an Untyped Object. Power Apps can handle this data type, but not as elegantly. You have no IntelliSense to easily use your results by typing the properties’ names.

Your stored procedure may return various outputs, from single parameters to complete tables.

To understand the SQL results, open your Monitoring Tool, execute your Stored Procedure, and check the results.

Here are the results from this stored procedure, which returns Table1 with values including is_success, error_message, and external_project_name.

"body": {
    "ResultSets": {
      "Table1": [
        {
          "is_success": true,
          "error_message": null,
          "external_project_name": 429681
        }
      ]
    },
    "OutputParameters": {}
  }

We'll update our Stored Procedure call to return results into a Table. Then we use the AddColumns function to add the desired columns, matching the Stored Procedure results. This is wrapped in the First function to return the first row, as we know there is only one.

UpdateContext(
    {
        tmpReturnedResults: First(
            DropColumns(
                AddColumns(
                    Table(
                        'kineTIC-dev'.dboSaveProjects(
                            {
                                Projects: JSON(colProjects),
                                SecondaryDepartments: JSON(tmpSelectedSecondaryDepts)
                            }
                        ).ResultSets.Table1
                    ),
                    IsSuccess,
                    Boolean(ThisRecord.Value.is_success),
                    Message,
                    Text(ThisRecord.Value.error_message),
                    JobNumber,
                    Text(ThisRecord.Value.external_project_name)
                ),
                Value
            )
        )
    }
);

We can now use the tmpReturnedResults variable in our code to do other things, including checking if it was successful.

If(
    Not(tmpReturnedResults.IsSuccess),
    Notify(
        tmpReturnedResults.Message,
        NotificationType.Error
    ),
    Notify(
        Concatenate(
            "Successfully created project ",
            " - ",
            tmpReturnedResults.JobNumber
        ),
        NotificationType.Success
    );