Answered

Power Apps Collection or Table Pass as Parameter for SQL Server Stored Procedure

(1) Share

Report

Posted on by RazenCee

Hi Team,

I have a Collection connected to a Gallery in Power Apps, whenever users click Run button then it will populate the Collection with some data and users can either edit, delete or add new rows.

Now I want to save this Collection into my Table in SQL Server. I can't just simply run the Patch function because I need to do some checking before updating the tables whether it's a new record or just update it or if it's missing in the collection then just delete the row in the SQL Server. I believe the checking of data will be much simpler in SQL rather than in Power Apps.

Is there a way to pass a Collection or Table into a Parameter of SQL Stored Procedure?

MyObjectInPowerApps.dbouspUpdateEmployeeTable({
EmployeeList: colEmployeesFromPowerApps
});

Like this code in Power Apps where EmployeeList is a User Defined Type Table parameter in SQL Server and the colEmployeesFromPowerApps is a Collection with same set of columns from the EmployeeList.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (2)

Verified answer

Michael E. Gernaey 53,433 Super User 2025 Season 2 on at

Like (2)

Report
Hi

Here you go.,

To pass a table to a SQL Server stored procedure from Power Automate, you can follow these steps:

Create the Stored Procedure: Ensure your stored procedure is designed to accept a table-valued parameter. Here's an example of how to create a stored procedure that accepts a table-valued parameter:

CREATE TYPE dbo.MyTableType AS TABLE ( Column1 INT, Column2 NVARCHAR(50) ); CREATE PROCEDURE dbo.MyProcedure ( @MyTable dbo.MyTableType READONLY ) AS BEGIN -- Your logic here SELECT * FROM @MyTable; END;

Set Up Power Automate Flow:

Trigger: Start by creating a new flow in Power Automate. Choose a trigger that suits your needs (e.g., when an item is created in SharePoint).

Add SQL Server Action: Add the "Execute a SQL stored procedure" action. This action requires a premium connector.

Pass Parameters: When configuring the action, you will need to pass the table data as a parameter. You can use a JSON array to represent the table data and pass it to the stored procedure.

Example Flow Configuration:

Initialize Variable: Use the "Initialize variable" action to create a variable that holds your table data in JSON format.

Execute Stored Procedure: Use the "Execute a SQL stored procedure" action and map the JSON variable to the table-valued parameter.

Here's a simplified example of how you might configure the flow:

```json { "Column1": 1, "Column2": "Value1" }, { "Column1": 2, "Column2": "Value2" } ``` In the "Execute a SQL stored procedure" action, you would map this JSON array to the `@MyTable` parameter.

For more detailed guidance, you can refer to resources like MSSQLTips and Power

Was this reply helpful? Yes No
RazenCee 41 on at

Like (0)

Report

Thanks @FLMike. Gonna look at this.

I'm still finding a way to keep it all in Power Apps but it seems I ran out of ideas and options so Power Automate again it is.

Was this reply helpful? Yes No
Verified answer

RazenCee 41 on at

Like (1)

Report
Just want to share the solution I made up for my case.

I'm not sure in performance basis but mine doesn't need Power Automate flow and another User-Defined Table Types in SQL Server.

Lets say in the Power Apps Collection you have 3 columns

Name

Age

Birthday

then you need to pass this Collection inside the Stored Procedure without passing it row by row.

Here are the steps:

1. In the SQL Server and inside your SP, you need to create a NVARCHAR (not sure about using VARCHAR) parameter that holds the JSON we are passing from Power Apps.

2. In the Power Apps you need to call your stored procedure and pass the json value of the collection like this: JSON(myCollection)

Inside your SP, you need to make this json back as a result set in SQL and do your logic.

3. Power Apps passes the JSON with additional special characters like backslashes, so in SQL we need to remove those.

SET @myJSONParameter = REPLACE(@myJSONParameter, '\', '')

4. Now for the result set:

SELECT Name, Age, Birthday FROM OPENJSON(@myJSONParameter) WITH ( Name VARCHAR(200) '$.Name', Age INT '$.Age', Birthday DATE '$.Birthday' )

It's up to you how you want to name the columns but the characters with Dollar signs should be the same as the name of the columns inside the JSON.

Hope this helps!

Was this reply helpful? Yes No