cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GRustean
Level: Powered On

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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
shailendra74
Level 10

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

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.

View solution in original post

4 REPLIES 4
shailendra74
Level 10

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

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.

View solution in original post

Community Support Team
Community Support Team

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

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
GRustean
Level: Powered On

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

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
Level: Powered On

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

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,940)