How to pass parameters to function in Powerapps to execute stored procedure in SQL server to return table for selection in Listbox.

[GRustean]

Hi,

I'm still a newbie to Powerapps and need some help/suggestions.

I am trying to build a Powerapps app for building up some metadata.

One of the options is to be able to allow the user to add some columns to a table in a SQL database A.

The list of available columns comes from a table in another SQL database B.

I have a stored procedure to show all the columns in B that are not currently in A. The procedure has 2 input parameters (@ConnectionID and @TableName) and creates a table containing the list of columns.

I want to have a way of executing this stored procedure supplying the 2 parameters.

Then I would have a View to read the created table and display it in a Listbox as I understand that you cannot execute a stored procedure from a View.

Any help would be appreciated. Thanks.

Regards,

George

[shailendra74 · Accepted solution]

Hi @GRustean 

Refer to below link to call SP from PowerApps (through MS Flow).

https://powerusers.microsoft.com/t5/Video-Gallery/Calling-Stored-Procedures-from-within-PowerApps/td...

You cannot call a stored proc directly from PowerApps.

Thanks.

[v-xida-msft]

Hi @GRustean ,

Do you want to execute a SQL Procedure from your PowerApps app?

Currently, within PowerApps, there is no direct way to execute the SQL Procedure from an app. As an alternative solution, I think the combination of PowerApps and MS Flow could achieve your needs.

On your side, you need to create a flow and use the "PowerApps" trigger button as the Trigger. Then you could consider specify the parameters you want to pass from your app within the "Execute stored procedure" action in your flow (Microsoft Flow), then add a "Response" action within your flow to return the "Execute stored procedure" action execution result back to your app.

Within your app, you need to add a Button control, set the OnSelect property to following formula:

ClearCpollect(
               ExecutionResult,
               'YourFlowName'.Run(Executestoredprocedure(V2)_ConnectionID, Executestoredprocedure(V2)_TableName)
)

you need to provide proper values for the Executestoredprocedure(V2)_ConnectionID argument and Executestoredprocedure(V2)_TableName argument within above formula.

When you press the Button, your flow would be fired. Then the result the "Execute stored procedure" action returned in your flow would be populated within the ExecutonResult collection.

Please check and see if the following video would help in your scenario:

https://www.youtube.com/watch?v=qb9Sloxw8wc

More details about returning an table value from a flow back to an app, please check the following blog or video:

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

https://www.youtube.com/watch?v=K_H5r0nzwy4

Best regards,

[GRustean]

Hi @shailendra74 ,

Thank you for your response. I took a look at the link and it was very helpful and I have been successful in implementing the Flow to call my stored procedure with parameters.

Regards,

George

[GRustean]

Hi @v-xida-msft ,

Thanks for your response.

I received the same link which helped me from @shailendra74 

Im my case I used the stored procedure to insert my resuts into a table and then in Powersppa, I used a View in a Lookup to show the results in my Listbox.

Regards,

George