Found a solution on this post: https://www.reddit.com/r/PowerApps/comments/1c8hipu/comment/l0ev6t7/?utm_source=share&utm_medium=web3x&utm_name=web3xcss&utm_term=1&utm_content=share_button
Be sure to read over the Microsoft article about the preview SP feature in Power Apps and that you've added your stored procedure to your Power App through the SQL connector.
https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections/connection-azure-sqldatabase#call-stored-procedures-directly-in-power-fx-preview
Your syntax for calling stored procedures is incorrect. It should be:
database.stored_procedure({args: values})
If you are expecting a return query from your stored procedure, then it will get sent over as an untyped object under the ResultSets.Table1 child. This is exactly the same return format if the same stored procedure was invoked from Power Automate.
In Power Apps:
database.stored_procedure({args: values}).ResultSets.Table1
In Power Automate:
body('Execute_stored_procedure_(V2)')?['ResultSets']?['Table1']
From here, you need to strictly type the untyped object in order to insert it into a collection, either using ForAll() or AddColumns().
ClearCollect(
colStoredProcedure,
DropColumns(
AddColumns(
Table(database.stored_procedure({args: values}).ResultSets.Table1),
Column1,
Text(ThisRecord.Value.TextValue),
Column2,
Value(ThisRecord.Value.IntValue),
Column3,
DateValue(ThisRecord.Value.DateValue)
),
Value
)
)As for invoking the stored procedure through Power Automate and sending it to Power Apps, the code is pretty much the same aside from needing to send the data over as a JSON string then wrapping your Run() in Power Apps with ParseJSON(). If your values are coming up blank, you need to troubleshoot what is happening through your flow.
To answer your questions:
The stored procedure feature is still in preview. As with all preview features, it's "use at your own risk" if you want to use it in a production environment.
Once you strictly type an untyped object and put it in a collection, you should be able to filter the data like any other normal collection.
