Send Collection to a Stored Procedure

[JimSutt]

I'm trying to insert the rows of a 2D collection into a SQL Server table.  This collection includes multiple columns and typically has 500+ rows.  This collection has the exact same column names as the target SQL Server table.  When using Collect(<SQL table>,<collection>) or ForAll(Patch(<SQL table>)), the inserts take roughly 1 sec/row, and therefore run for over 5 minutes. 

My alternate approach is to pass the entire collection to Power Automate to execute a stored procedure to perform the inserts faster.  Hopefully this approach is more efficient.  There are several guides (example: link) that address this for a single column array, but I cannot find an example of this as a 2D array.

Does anyone have experience passing a 2D collection into a stored procedure using Power Apps?

[Pstork1]

If the records are in a collection you can pass the collection to Power Automate using the JSON() function to convert it to a JSON array.  Then in Power Automate you can do a Parse Json to access the properties of the records in the array.  Passing it to the array depends on what parameters the stored procedure is looking for.  But even if the stored procedure is only looking for one record at a time you could run the stored procedure in a loop and apply the values in the JSON array to the stored procedure one record at a time.  But the stored procedure in the video is using a multidimensional array in SQL as input.

[JimSutt]

Thank you @Pstork1 
Is it possible to write a stored procedure called by Power Automate in which I pass both a static value and a JSON array from PowerApps to bulk load into SQL Server?  In this scenario, I have a JSON array of 100+ items and a ID field to cross reference these values to a header record.  The JSON array is pulled from a column in my collection and is formatted such as [{"X":123456},{"X":232244}, ...].  I wrote the following stored procedure, but combining a single variable and a JSON array doesn't seem to play nice.  I am trying to avoid adding the static value to the JSON array due to size limits with the NVARCHAR(MAX) declaration.  

Spoiler

CREATE PROCEDURE [dbo].[ITEM_SCAN_INSERTS]
@HEADER_SCAN_ID int,
@JSONnvarchar(max)
AS
INSERT INTO [dbo].[ITEM_SCAN] (HEADER_SCAN_ID, ITEM_SCAN)
VALUES
(
@HEADER_SCAN_ID,
(
SELECT X
FROM OPENJSON(@JSON)
WITH (X int)
)
)
GO

[Pstork1]

Yes, you can pass multiple parameters to the flow.  One can be a JSON array and another a simple value.  Then as long as the stored procedure is written with multiple parameters you can supply the values passed to the flow as those parameters.

[JimSutt]

Yes, I am aware of passing multiple values to Power Automate, but I have not had any luck finding a template for a stored procedure that can handle both JSON array and a simple value.  The example code I provided above fails.  

[Pstork1]

This really isn't a Power Apps or Power Automate issue.  The problem is how to integrate the single parameter into the JSON data inside the stored procedure.  I'm not sure how to do that. I was able to create a stored procedure that takes both a single and JSON parameter.  I just couldn't figure out how to insert the single parameter into the JSON table once the stored procedure had both.

/****** Object:  StoredProcedure [dbo].[PersonInsertJson]    Script Date: 5/21/2021 10:46:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PersonInsertJson](@json NVARCHAR(MAX), @age int)
AS BEGIN
  INSERT INTO [dbo].Person (id, firstName, lastName, age, dateOfBirth)
  
  SELECT id, firstName, lastName, age, dateOfBirth
  FROM OPENJSON(@json)
       WITH (id int, firstName nvarchar(50), lastName nvarchar(50), 
	   age int, dateOfBirth datetime2)
END

[JimSutt]

Hi @Pstork1 , I agree, my problem is apparently not with PowerApps or Power Automate.   I also am unable to figure out format of the stored procedure as you indicated in your last post.  To compound the issue, I have now learned that my company is running a pre-2016 version of SQL Server, so the OPENJSON function is not available to me.  I'm going back to the drawing board on this.  My challenge is to determine a more efficient way to bulk load rows into SQL Server 2014.  Both Collect() and ForAll(Patch()) seem to call SQL Server, insert a row, respond to PowerApps, repeat.  Since there is a call and respond for each row, this functionality in PowerApps will run for several minutes when performed on a collection of 100+ rows.  

[Pstork1 · Accepted solution]

I played around with the stored procedure and did get it to work.

/****** Object:  StoredProcedure [dbo].[PersonInsertJson]    Script Date: 5/21/2021 10:59:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PersonInsertJson](@json NVARCHAR(MAX), @age1 int)
AS BEGIN
  INSERT INTO [dbo].Person (id, firstName, lastName, age, dateOfBirth)
  
  SELECT id, firstName, lastName, @age1 int, dateOfBirth
  FROM OPENJSON(@json)
       WITH (id int, firstName nvarchar(50), lastName nvarchar(50), 
	   age int, dateOfBirth datetime2)
END

  Not sure if this helps, but it does work. 

[Pstork1]

You can still use JSON and flow to do this.  Pass the JSON to the flow, User Parse JSON to turn it into a readable array, Run an Apply to Each on the Array, Insert the rows into the SQL server.  It will still do the rows one at a time, but it will all be in the context of the flow with no back and forth with Power Apps.

[takolota]

Anyone with a similar use-case can also try using this batch SQL CRUD template: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/SQL-Batch-Create-Read-Update-and-Delete/...