SQL Stored Procedure Supported In Power Apps
HTML-код
- Опубликовано: 14 май 2024
- Hey Everyone,
SQL Stored Procedure Supported In Power Apps is available in preview! This is great news for everyone who uses SQL as their primary data source and has stored procedures that need to be executed directly from the canvas app.
In this video I will go over an existing SQL stored procedure that I have, how in the past I used Power Automate to trigger it and now I switch over to doing it directly from Canvas apps.
Table of contents
Introduction 00:00
Current situation 01:00
Directly add stored procedure
Requirement 02:42
Connect to SQL stored procedure 03:00
Turn on preview 03:27
Connect to SQL stored procedure 03:42
Execute stored procedure 4:38
Present the data 07:11
Full review 10:31
Conclusion 11:45
Helpful link(s)
learn.microsoft.com/en-us/pow...
learn.microsoft.com/en-us/pow...
#PowerApps #NewFeature #SQL #StoredProcedure
Contact information:
Twitter: / dchristian19
Facebook: / www1.christianfamily.biz
Instagram: / dan.christian.33
LinkedIn: / danchristian19
Join this channel to get access to perks:
www.youtube.com/@DanielChrist... 
Наука
Connect with me:
☕Buy me a coffee: www.buymeacoffee.com/danchristian
🖇LinkedIn: www.linkedin.com/in/danchristian19/
🚀 Instagram: instagram.com/dan.christian.33/
X (Twitter): twitter.com/dchristian19
I can't tell you how exciting this is. Calling the Stored Procedure directly is a game changer for me and others trying to figure out how to incorporate PowerApps into their portfolio of development tools. Creating multiple flows to execute different Stored Procs, in multiple canvas apps is time-consuming and potentially error-prone. Thank you for publicizing this new feature.
On my collection it shows as a ResultSets and has a Question mark ? below, but it doesn't show as a Table1 next? Any ideas?
I got this too. Here's what it looks like in the data>response section in Monitor:
"body": {
"ResultSets": {
"Table1": [
The feature currently does not appear to have any support for output parameters or Return codes, so as a workaround you include a SELECT statement in your proc and the results will appear in Table1. Note that a proc can have multiple SELECT statements, so then you would get Table2, etc. one for each SELECT in your proc.
You have to be careful not to have conditional SELECTs (e.g. SELECTS that sometimes fire and sometimes do not) or you won't know which Table the results end up in.
Note that after any change to the output of a proc you need to remove it and re-add from Power Apps - currently there is no Refresh option.
This is my problem too. If you run the Monitor (from Advanced Tools) you will see the data in the SP Response tab.
I should clarify: I do see all of the data in the Monitor data>response section, and the Procedure is a normal SELECT with no output parameters or the like. So it seems that everything comes back from SQL Server fine. I just can't seem to access it within the app.
Great video, thank you Daniel! Could you please consider making a video demonstrating how to use a stored procedure to update or insert records? Thank you in advance.
Very informative and thanks for sharing. I rarely use SQL but good to have this knowledge in my "bank" for the future.
Hi Daniel, Maybe you can set the Gallery Items to First(ResultSets).Value so you do not need to nest galleries?
Hi Daniel - thank you for your very helpful video. This looks like a powerful technique. I am very new to PowerApps but I know SQL Server well. I followed your example closely and it worked but when I inspected the variables there was no result set or table. I ran a monitor against the app and I could see all the data in ResponseSets.Table1 - but it does not appear in the variables collection or in the gallery. Any ideas?
Hi danial , thanks for the amazing hints ,i have an inventory app and i have a lot of procedures to call through power automate,to insert and update the items,i am pretty sure doing that from powerapp is much quicker and i don't know ,this feature is in preview, which means i can use it on production environment,right?
Great Work. Thanks
Can we create SP in Dataverse? How?
Hi. I'm very excited for this new feature since it enhaces drastically the functionality available for the apps I made. I've been testing this new feature for several days, however I found something strange. If I execute my stored procedure from PowerApps studio while editing the app, everything goes as expected, however, after publishing the app, I get an error that says that mu stored procedure is not allowed. Am I missing something about permissions? Did you try/experienced this same error? I'm using Azure SQL DataBase btw. Thank you!
Need more videos ❤
You got it!
I ran SQL profiler and run flow and then stored procedure in power app. And it do the exact same sql query for both methods. The only "broker" from this line exclude in logic -is the flow APIs functions I think. PA REST API -> AZURE SQL server and second PA REST API-> FLOW API->Azure SQL Server. The duration of operation via PA store procedure is less then via flow
This is awesome news, but there seems to be an issue with it running outside of development mode. Once you publish and run it, even in a development environment, it fails. 403 - "Procedure s_XXXXX is not allowed". Not sure if there is some sort of permission missing, but the access is identical.
Doesn't look like there is any support for output parameters. I tried passing a context variable of the appropriate type into the stored procedure call in PowerApps, but its value didn't get updated. You *can* return values via a SELECT in the Proc but this means you have to modify/tailor your procs specifically to work with Power Apps which is less than ideal and given the many years this preview has taken to appear; I don't expect this shortcoming to be addressed any time soon.
I can only get an output parameter back from a Stored Proc when I use the Flow approach, which does support returns. For that reason, I'm actually disappointed somewhat to learn that this new "native" approach to executing stored procs appears to return only table variables, which can be used in galleries. It seems pointless to go to that extent just to retrieve a single output value.
I guess this means that there are two use cases. One, using stored procs executed by Flows, can return output parameters. The other, using the new native stored proc approach, can only return tables. I am looking for a real business case for this option, though.
@@GroverParkGeorge If I control the SP, I put my output parameters in the return select of the store proc. If the store proc does not return any data, i do something like this: "SELECT OUtput1, Output2, Output3"
In powerapps you only need to put the result in a collection and get the First(). In my opinion, this process is simpler than using flow. But if you don't control the SP and can't make any changes, I'm with you, I don't understand why Microsoft gives us a half-baked solution. In real life a SP as a return value, input parameters,possibly Output parameters and possible one or multiple resultsets. So this feature should address all these features.
I want to draw shipping routes for my Power app project. could you create a video.
I think you can get rid of the extra gallery if you add a .ResultSets.Table1 Like this:
OnSelect = ClearCollect(ColNewSQLReturn; Reporting.dboapiFinnTest(blahblahblah).ResultSets.Table1)
Worked for me.
I also skipped the Clear(ColNewSQLReturn) since that was just being flagged as an error and also deleting my variable. ...why did you even do that? Is this a stupid n00b question and/or is it something you erroneously neglected to mention in the video when you did it?
Suggestion: for a video like this do TWO example implementations. The first one simpler, without any parameters on the on the Stored Procedure. The Stored Prodecure is just doing a JOIN or something and returning a Table. Second example maybe even slightly more complicated than this one.
Thanks for the video. Keep it up.