Power Platform Community Forum Thread Details

I have to insert rows from csv document into database table, previously doing some validation. Anyhow the flow is running around 40 minutes for 200/300 rows and it is really slow. So we decided to create a store procedure and send all rows as a list or array and do the logic inside it.

But I cannot find a way to send a list/array/json from Power automate as an input parameter to the store procedure.

Usually I am getting this mistake: "Database functions with non-scalar parameters aren't supported.
inner exception: Database functions with non-scalar parameters aren't supported."

For example here is the store procedure:

ALTER PROCEDURE [dbo].[spInsertEmployees]
@EmployeeData NVARCHAR(MAX)
AS
BEGIN
-- Process the JSON data here
INSERT INTO [dbo].[Employees2] (Id, FullName, Gender)
SELECT Id, FullName, Gender
FROM OPENJSON(@EmployeeData)
WITH (
Id INT,
FullName NVARCHAR(50),
Gender NVARCHAR(50)
)
END

And here is the json I am sending as a parameter from Power Automate:

[

{

"Id": 2,

"FullName": "John Doe",

"Gender": "Male"

{

"Id": 3,

"FullName": "Jane Smith",

"Gender": "Female"

}

]

Any idea/suggestion how to do this, or some workaround? Also I tried to create a store procedure with input parameter as Table value and created a new type for it, but I still have the same mistake from Power Automate.

Categories:

General topics