Power Apps SQL Stored Procedures

As of March 9, 2024, Microsoft has enabled calling Microsoft SQL Stored Procedures directly from within Microsoft Power Apps. 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 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.
• Under New 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.

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

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 generally 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 coming back from SQL look like. To achieve this, open your [[Monitoring 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 Table1 with several 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 want to update our Stored Procedure call to return the results into a Table. Then we use the AddColumns function to add the columns we want, matching the results returned from the Stored Procedure. This is wrapped in the First function, which returns only the first row (since there is only one in this case).

// =================================================================================
// Call SaveProjects and save the results to a temp variable.
// =================================================================================
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
            )
        )
    }
);

Now, we can use the tmpReturnedResults variable in our code to do other things, including checking whether it was successful.

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