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

So, as I said, the format is incorrect.

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;
  1. 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] ] 
    } 
}