PowerApps - Pass string array to SQL Stored Procedure from PowerApps using Flow (Tutorial)
HTML-код
- Опубликовано: 4 июн 2024
- In this PowerApps Tutorial we look at how to query SQL tables from PowerApps and more specifically by using the infamous 'in' operator against an array! Unfortunately this is not support directly from PowerApps (yet), and SQL Queries are not supported (yet) to on-premises SQL servers so we have to use Flow to trigger stored procedures.
We will be using Flow to call the stored procedure in SQL with a string parameter, have SQL split the string parameter and join the required data, and then return the data to PowerApps. It is amazing how performant this is and the amount of records that it can safely handle in the response.
In another video we use direct query which is only available if the SQL server is directly accessible from the internet, and does not use a on premises data gateway:
• SQL Query from Power A...
Thanks for the great video! I do appreciate those little details you gave for each step. It saved hours of my work and tons of energy. Details are key.
Simply Superb! Excellent clarity on the subject and hence easy explanation. Thanks for sharing!
Well done. Easy to workout. Cheers
Dave this is finally being supported natively in powerapps with the release wave 2 2023 release
Hi
This is Really helpful as i wanted to customize my flow on certain conditions,
Thank so much. Hope you can make a video on Variable Specially in Flow, Also inculde that how use to variable to increase the number by 1 when each form id added in sharepoint as the Default ID in sharepoint is not reset to 1.
And this i havent seen anywhere on youtube and in article, will really help Millions of people who are having query when the form is subimitted in SHarepoint and MsFroms. However Certain Articles does have the solution but are not upto the mark in real scenarios.
Thanks
Tapesh
Thank you for thsi wonderful video! For some reasons my Collection shows empty after running the flow. The Flow by itself is returning data when I test it on the Flow site separately. Not sure if it has anything to do with licensing.
Thanks for the video. I have one question if you can answer it. So instead of a button can we have a search box for the user to specify what value they want to search for ?
Hi Dave, That's a great vedio. I am trying to do similar using Power BI. Can I pass parameters from Power Apps to Power BI direct query (stored procedure)?
Goode Video Dave! The error message in PowerApps, if you leave mandatory fields in Json, comes from fields with "null" values as a result from the SQL query, that's my experience. Another TIP, always define the variable for the filter as the last step in the flow, otherwise you often don't see the Resultsets table1, and you'll have to add that manually.
I don't have table 1. What do you mean define a variable for the filter as the last step? I have variables defined for all my paramos to the SP. still no Table1"
Hi Dave, Flow does support Stored Procedure calls over On-Premises Data Gateways. Not sure if this was introduced since your video.
1) In Flow, from your 'Execute stored procedure (V2)' step, select the ellipsis menu, top right.
2) At the bottom of the context menu, select '+ Add new connection'
3) In the 'Authentication Type' drop-down, select either 'SQL Server Authentication' or 'Windows Authentication' to reveal the Gateway options
Hope it helps
- Kaine
Hello Kaine, thank you for taking the time to send this info. You are correct, Flow does support Stored Procs through the DGW. As far as I am aware this is what is mentioned in the video as well (at around 00:35). What is not support by Flow through the DGW is SQL queries. Please let me know if there is somewhere in the video where I am not stating these facts correctly.
@@DavesTechTips Apologies Dave, I may have made an assumption. Anyway, hopefully it will help someone... Stay safe
Absolutely no problem man, thanks for taking the time to reach out. Have a great day and keep well, stay in 👍
@@DavesTechTips Haai Dave, dink jy hierdie funktionaliteit is al beskikbaar? Dit voel vir my asof dit 'n ongelooflike populere aanvraag is, maar vir een of ander rede wil Microsoft eenvoudig nie dit aanspreek nie. Kan mens nie egter 'n query in 'n stored procedure sit en dan dit so run met flow nie?
Groete.
Hello Dave, I'm getting the following error when trying to pass a string variable through trough the SQL stored Procedure variable.
Microsoft SQL: Line 1: Length or precision specification 0 is invalid.
Must declare the scalar variable "@Variable0".
The string variable is simply "EMTALA" and the query works fine in SSMS. Its pretty similar to what you have in this video. Thanks for the help
What’s the editor you are using??
Great video Dave - thanks a million. One point i cannot get working is on 11:18 you select ResultsSet Table1 from the dynamic content list (right hand side). I am using the version online as of today and i do not get this, but just the ResultsSet without the Table1. Any ideas what i am doing wrong? I have now tried this 3 times with the same each time. Thanks for your help.
I struggled with this same problem for hours and eventually found a work-around by adding a Parse JSON as s step between Execute stored procedure and Response, but I suppose Initializing Variable as last step might also work.
@@synaptocrash277 I have the same problem. Worked fine on an earlier flow. What did you do in the parse json and response steps?
having same issue please help
@@synaptocrash277 Hi! Not sure if you can read this but can you share how you did the parse JSON step
I have three parameters I'm sending to a stored procedure. I created the initVar... steps as outlines, and when I test the flow Power Automate lists 9 variables I have to fill out. If I delete those steps and use the Ask In Power Apps in the Execute Stored Procedure step, I have to fill out 5 required variables.
Hi, this video is very helpful , but I'm facing issue . I'm following every steps you mentioned.. I'm not passing any parameter from powerapps. When I'm collecting the response back in collection I'm getting boolean value as true. I can see the output in Automate but it's coming as boolean in collection.. I have been struggling with this issue since two days.. please let me know I'm going wrong. What approach I should follow.. I'm able to add the records to sharepoint list it works there but not in powerapps.. please help me
Reading the database is fine but this does not answer how to write to a SQL table using a stored procedure. What's the point of a connector if we can't write data to our on-prem database?
Apparently, the outputs are different in the production version of this action compared to the preview. no such luck with the Table1 option. I put it in as the flyover expression, and its a no go. Anything that requires schema is always a mystery of syntax, and luck when you get the right combination of syntax between body, output, and schema. Its one of the most frustrating parts of power automate, and this completely breaks the development without needing to be a programmer vision of PA.
Hi Dave. Awesome guide! I was just wondering @11:17 ruclips.net/video/BAGBzI4zdww/видео.html you mentioned an old expression to get table 1. What Is that expression? I am trying to use the connector with azure db, and it does not seem to pick up the metadata from it automatically (as is often the case with azure db).
Got it working by using this expression: body{'Execute stored procedure (V2)'}['resultsets']['Table1']
I am trying to do something similar using MS Flow Power Query. I have the Power Query and Response configured, and in Flow they collect the data as I expected, but I haven't been able to get them into PowerApps. Because I don't have any init variables, I just used ClearCollect(PowerQueryResults.myflow.Run()) but that didn't bring any results.
Here is a subset of the results in the Flow Response Out (there are about 12000 records total)...
{"statusCode":"200","schema":{"type":"array","items":{"type":"object","properties":{"Index":{"type":"integer"},"Product_Location_Id":{"type":"integer"},"Item No_":{"type":"string"},"Unit of Measure Code":{"type":"string"},"Item Category Code":{"type":"string"},"Product Group Code":{"type":"string"},"QTY":{"type":"integer"},"Product_Description":{"type":"string"},"Product_ProductLine_Id":{"type":"string"}}}},"body":{"resultType":"JsonValue","value":[{"Index":5,"Product_Location_Id":1735,"Item No_":"CAP-PD-160","Unit of Measure Code":"EA","Item Category Code":"04-04","Product Group Code":"PLASTIC","QTY":60.0,"Product_Description":"#16 PLASTIC PLUG","Product_ProductLine_Id":"187"},{"Index":9,"Product_Location_Id":1735,"Item No_":"CAP-PD-80","Unit of Measure Code":"EA","Item Category Code":"04-04","Product Group Code":"PLASTIC","QTY":50.0,"Product_Description":"#8 PLASTIC PLUG","Product_ProductLine_Id":"187"}]}}
To me that looks like the proper output.
Within PowerApps I do see the proper headers in the Collection, just no data.
Do you know if method works with Power Query?
having same issue
13:25 ask in Power app not showing in my Power Automation
Can Anyone help.
HTTP 200 Response is not intuitive at all. And you also have to know to generate the schema from the table. I just hate when Microsoft make things needlessly complicated. The "correct" flow should be Powerapps -> Run Stored Proc , Query , etc -> Respond To Powerapps. I don't see why they can't make it this way instead.
Then in code simply ClearCollect(MyCollection,MyFlow.Run(Parameters)) and columns should be there.
The "Run stored procedure" is already limited to return a single table at most, and cannot collect output vars from SQL.
Hi Daniel. Thank you for the feedback and I agree, there is room for improvement on this and I am sure that PowerApps will be able to directly send queries to SQL soon. What I do enjoy about PowerApps is that there is usually a work around for current limitations by making use of other modules like Flow. (If you have access to the licenses for it)
How can i pass 2 parameters into stored procedure?
yes