# MSSQL-PLUS - passing array to UDT parameter (TVP param in a stored procedure)

**URL:** <https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049>\
**Category:** General\
**Created:** [14 July 2020 11:31 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049 "2020-07-14T11:31:11Z")\
**Posts on this page:** 1\
**Showing post:** 22

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [23 July 2020 12:17 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/22 "2020-07-23T12:17:40Z")

</div>

So, as I said, the format is incorrect.

> [@Steve-Mcl](#):
>
> The format of the TVP parameter must be like this...
> 
> ```auto
> {
> "columns": [
> {
> "name": "a",
> "type": "VarChar(50)"
> },
> {
> "name": "b",
> "type": "VarChar(50)"
> }
> ],
> "rows": [
> ["row 1 col 1", "row 1 col 2"],
> ["row 2 col 1", "row 2 col 2"],
> ]
> }
> 
> ```

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/6/c69f6db17a8a18958ebde72a7b05d80f16e9a426.png)

Your `UDT_Rolemanagement` parameter is set to get data from `msg.payload` - a TVP MUST be formatted as I have pointed out.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/b/7b66b34b6271789d43a5c9022fa4661304cd8fe2.png)

* * *

#### Try this.

1. Change the UDT parameter value to `payload.table`;  

2. BETWEEN the `switch` node and `MSSQL`, add a `function` node with this code inside...

```auto
//convert the payload array of objects to a flat array
var rows = msg.payload.map(e => [e.functionid, e.Accessid]);

//get the id and name from the payload.
var id = msg.payload.id;
var name = msg.payload.name;

//setup the table and its columns
var table = {};

table.columns = [
        {
            "name": "functionid",
            "type": "int"
        },
        {
            "name": "Accessid",
            "type": "int"
        }
    ];

//add the rows to the table
table.rows = rows;

//create a NEW payload with .id, .name and .table properties
msg.payload = {
  id: id,
  name: name,
  table: table
}

//return the updated message to the next node
return msg;

```

1. Add a debug AFTER this function node and show me the output - it should look like this...

```auto
{ 
  id: 0,
  name: 'a3',
  table:
   { 
      columns: [
        { name: 'functionid', type: 'int' },
        { name: 'Accessid', type: 'int' },
      ], 
      rows: [[2,1], [3,1] ] 
    } 
}

```

---

_[View the full topic](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049)._