cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

Hi @Anonymous 

 

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-p/161897

 

You cannot call a stored proc directly from PowerApps.

 

Thanks.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi @Anonymous 

 

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-p/161897

 

You cannot call a stored proc directly from PowerApps.

 

Thanks.

View solution in original post

v-xida-msft
Community Support
Community Support

Hi @Anonymous ,

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.
Anonymous
Not applicable

Hi @Anonymous ,

 

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

Anonymous
Not applicable

Hi @v-xida-msft ,

 

Thanks for your response.

I received the same link which helped me from @Anonymous 

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,895)