# 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:** 4\
**Page:** 2

<div class="post-metadata">

**Author:** ![dhanasekar](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@dhanasekar](https://discourse.nodered.org/u/dhanasekar)\
**Post date:** [23 July 2020 11:44 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/21 "2020-07-23T11:44:42Z")

</div>

Please check the below images.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/f/9f5a85528f0c2d794ddf6b5e92826a1d39035214.png)  
Same payload array passed to TVP parameter  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/9/095f9d8a53f765c4102c03a5c79d5c9962276be8.png)  
Change node rules are,  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/9/39ef6436b0813a938e5f922773c15f8e59cc9f94.png)

---

<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] ] 
    } 
}

```

---

<div class="post-metadata">

**Author:** ![dhanasekar](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@dhanasekar](https://discourse.nodered.org/u/dhanasekar)\
**Post date:** [23 July 2020 12:47 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/23 "2020-07-23T12:47:31Z")

</div>

Wow. It is working like a charm.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/4/443fc2282d8aecad8821206251170ec5c98b1488.png)

Thank you @Steve-Mcl for your continuous support.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [6 August 2020 12:47 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/24 "2020-08-06T12:47:35Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.

[Previous page](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049.md?page=1)