PowerApps - Pass string array to SQL Stored Procedure from PowerApps using Flow (Tutorial) 

Simply Superb! Excellent clarity on the subject and hence easy explanation. Thanks for sharing!

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.

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

Dave this is finally being supported natively in powerapps with the release wave 2 2023 release

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 ?

Can do it directly in PowerApps now. Works great, though still in preview as of July 2024.

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.

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

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)?

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.

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

Well done. Easy to workout. Cheers

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.

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?

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??

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.

13:25 ask in Power app not showing in my Power Automation Can Anyone help.

Hi Dave. Awesome guide! I was just wondering @11:17 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-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).

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?

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.

How can i pass 2 parameters into stored procedure?