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

[dhanasekar]

Please check the below images.

Same payload array passed to TVP parameter

Change node rules are,

[Steve-Mcl]

So, as I said, the format is incorrect.

@Steve-Mcl

The format of the TVP parameter must be like this...

{
    "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" ],
    ]
}

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

---

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...

//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;

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

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

[dhanasekar]

Wow. It is working like a charm.

Thank you @Steve-Mcl for your continuous support.