# 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:** 20\
**Page:** 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:** [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/1 "2020-07-14T11:31:11Z")

</div>

Hi,  
I am trying to execute stored procedure which is having UDT parameter using node-red-contrib-mssql-plus node.  
the query i used is,

```auto
exec Rolemaanagement '{{{payload.name}}}',{{{payload.id}}},{{{payload}}};

```

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

The msg.payload has array data for UDT parameter.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/1/c1de02d67361b63d5b4265ed1a3b92b9cd24fbbd.png)

And I am getting the below error from MSSQL node.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/1/213c238eddddc207a5ea2a8df8bbe9603a5a40fb.png)

I don't know how to send array object as parameter in MSSQL. Kindly share your inputs if anybody knows the solution.

---

<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:** [14 July 2020 14:01 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/2 "2020-07-14T14:01:52Z")

</div>

As you can see from `msg.query` you are not sending valid SQL.

What does a valid SQL statement look like for that stored procedure?

---

<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:** [14 July 2020 14:18 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/3 "2020-07-14T14:18:52Z")

</div>

Thanks for your reply @Steve-Mcl.  
The same procedure i have executed successfully in c#.Net platform. In this I am passing DataTable as parameter value.

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

I am using visual studio to execute this code. DT is a object for DataTable class. And it's declaration statement looks like `DataTable DT = new DataTable();`  
below image shows the debug output of DT

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

In node-red, msg.payload having the same table data.  
Hope this information is sufficient.

---

<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:** [14 July 2020 14:30 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/4 "2020-07-14T14:30:07Z")

</div>

The node doesn't support parameters - you must generate valid SQL strings.

So can you do this with standard types (strings, numbers etc) in a normal SQL statement? If yes, you can do it with this node.

---

<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:** [14 July 2020 14:58 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/5 "2020-07-14T14:58:03Z")

</div>

thanks @Steve-Mcl.  
hope this features will be added in further updates in mssql node.  
So that we can execute more complex sql queries also.

---

<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:** [14 July 2020 16:29 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/6 "2020-07-14T16:29:52Z")

</div>

I'm gonna look at the complexity- see if I can implement this easily.

I know the underlying libraries do support it but I have to figure out how parameters types will be created at node-red side and passed to the command. It has to be easy for the user otherwise I'll get inundated with questions.

---

<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:** [16 July 2020 14:27 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/7 "2020-07-16T14:27:42Z")

</div>

Coming soon...

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/2/22eff5549e90ec9c4869ceab00ec9f8a9ef8fbe2.png)

---

<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:** [16 July 2020 15:17 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/8 "2020-07-16T15:17:13Z")

</div>

Hi @Steve-Mcl,  
Good to see this. your post confirms parameter type is varchar and integer. can we send the complete object array as a parameter?. so that we can send table data to UDT type parameters also.

---

<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:** [16 July 2020 15:35 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/9 "2020-07-16T15:35:48Z")

</div>

UDT is one of the options but I need help testing that.

Would you be willing to beta test?

---

<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:** [16 July 2020 15:40 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/10 "2020-07-16T15:40:49Z")

</div>

Sure @Steve-Mcl. I'll test and give you the feedback.

---

<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:** [18 July 2020 02:24 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/11 "2020-07-18T02:24:22Z")

</div>

Hi @dhanasekar

In GIT now, under branch `params` ([link](https://github.com/bestlong/node-red-contrib-mssql-plus/tree/params))

I have contacted a co-author regarding publishing a beta to NPM but until then, you can install from github if you feel up to it? you can `git clone https://github.com/bestlong/node-red-contrib-mssql-plus.git` and `npm install ~/node-red-contrib-mssql-plus` - OR - install direct from github `npm install bestlong/node-red-contrib-mssql-plus#params --save`

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

### Notes...

UDT and some other types are "not supported" (this is a `tedious` driver limitiation)  
TVP can only be used with stored procedure (this is `tedious` driver stipulation)  
Using the example sql type and procedure [here](https://tediousjs.github.io/node-mssql/#table-valued-parameter-tvp), you would need data in the value to be in the following format...

```auto
{
    "columns": [
        {
            "name": "a",
            "type": "VarChar(50)"
        },
        {
            "name": "b",
            "type": "Int"
        }
    ],
    "rows": [
        ["hello tvp", 777],
        ["bye tvp", 888],
        ["call the cops", 999]
    ]
}

```

this flow works with the above example...

```auto
[{"id":"3eb71b1f.47fdb4","type":"MSSQL","z":"595a5dd5.a963a4","mssqlCN":"a51e405c.10f64","name":"","query":"MyCustomStoredProcedure","outField":"payload","returnType":"1","throwErrors":"0","queryMode":"execute","params":[{"output":false,"name":"tvp","type":"TVP(TestType)","valueType":"json","value":"{\"columns\":[{\"name\":\"a\",\"type\":\"VarChar(50)\"},{\"name\":\"b\",\"type\":\"Int\"}],\"rows\":[[\"hello tvp\",777],[\"bye tvp\",888],[\"call the cops\",999]]}"}],"x":500,"y":520,"wires":[["bfde3540.a91018"]]},{"id":"614325ad.afd22c","type":"inject","z":"595a5dd5.a963a4","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":500,"y":480,"wires":[["3eb71b1f.47fdb4"]]},{"id":"bfde3540.a91018","type":"debug","z":"595a5dd5.a963a4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":510,"y":580,"wires":[]},{"id":"a51e405c.10f64","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"Local SQL","server":"192.168.99.99","port":"1433","encyption":false,"database":"testdb","useUTC":false,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false}]

```

---

<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:** [18 July 2020 13:35 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/12 "2020-07-18T13:35:04Z")

</div>

@dhanasekar I have further updated the github repository (now V0.5.0-beta.1)

It includes a demo flow (import -\> examples -\> mssql-plus -\> tvp)

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/2/926daa511ace39e1e985c0ed490dedf2bf36749d.png)

Installation is same as previous post.

Please feedback.

---

<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:** [19 July 2020 02:54 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/13 "2020-07-19T02:54:23Z")

</div>

Dear @Steve-Mcl,  
I am not able to succeed with the node installation. I have tried like the picture shown below.

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

and also I have downloaded files from github and tried like below,

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

I am a newbie for github. Please correct if I am wrong.

---

<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:** [19 July 2020 08:55 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/14 "2020-07-19T08:55:21Z")

</div>

Hi. Don't worry about it. I am preparing a better beta which I will publish to npm. I didn't like the radio buttons (due to the override method I had to employ) so I'm changing things to typedInputs to provide clearer functionality and more flexibility.

Should be ready later today.

---

<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:** [19 July 2020 20:45 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/15 "2020-07-19T20:45:30Z")

</div>

@dhanasekar

I have now published a [beta version](https://www.npmjs.com/package/node-red-contrib-mssql-plus/v/0.5.0-beta.1) to NPM

You can install like this...

1. stop node-red
2. open a command window
3. `cd` your `.node-red` directory e.g...
  1. `cd c:\Users\vstoresft2\.node-red`

4. `npm uninstall node-red-contrib-mssql-plus`
5. `npm install node-red-contrib-mssql-plus@beta`
6. start node-red

As before - "It includes a demo flow (import -\> examples -\> mssql-plus -\> tvp)"

Please feedback

---

<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 07:13 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/16 "2020-07-23T07:13:11Z")

</div>

@dhanasekar did you have success with this?

please feedback.

---

<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 09:00 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/17 "2020-07-23T09:00:52Z")

</div>

Hi @Steve-Mcl,

Sorry for the delay in response. After installation npm install node-red-contrib-mssql-plus@beta. I am not able to start node-red successfully.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/4/544ee451789239ddb9cd38ae5767985e734c09cb.png)  
I have just followed the same instruction given in your previous reply. Am I missing something?

---

<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 09:15 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/18 "2020-07-23T09:15:23Z")

</div>

Not good. I assume it was working immediately before?

NOTE: dont worry, all is not lost (we can fix this)

First, please make a backup of your flow files from the .node-red folder (they will be called flowsxxxx.json)

On to your issue - please check this thread by @knolleary (there is an edge case he wishes to track down - you appear to have hit it)

> [@Windows 10 \[error\] Failed to start server: \[error\] TypeError: Cannot read property](https://discourse.nodered.org/t/windows-10-error-failed-to-start-server-error-typeerror-cannot-read-property/18600/8):
>
> Hi @dougcr4 this is a rare issue that we've never managed to get to the root cause of. If you are still hitting it, could I trouble you to add a bit of debug into the code to help us figure it out? Specifically, if you edit the file C:\Users\Dell\AppData\Roaming\npm\node\_modules\node-red\node\_modules@node-red\registry\lib\registry.js and look at line 186 you should see: set.types.forEach(function(t) { if (nodeTypeToId.hasOwnProperty(t)) { Please add the …

if you edit the file `C:\Users\vstoresft2\AppData\Roaming\npm\node_modules\node-red\node_modules@node-red\registry\lib\registry.js` and look at line 186 you should see:

```auto
                set.types.forEach(function(t) {
                    if (nodeTypeToId.hasOwnProperty(t)) {

```

Please add the following immediately before it:

```auto
                // START DEBUG
                if (!set.types) {
                    console.log("Module:",module.name);
                    console.log("Path:",module.path);
                    console.log("Set:",setName);
                }
                // END DEBUG
               

```

The next time you restart Node-RED you'll get a bit more information in the log about what node module its loading when it hits the error

* * *

I will follow up shortly on how we can get you running once more

---

<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 10:43 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/19 "2020-07-23T10:43:07Z")

</div>

Hi @Steve-Mcl,

Node-red started after deleting ui-etable node.  
Now I have checked with TYP type parameter. It shows invalid table error. But the msg.payload has proper table value in object array format. This is my configuration,

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/a/1ae07f1f1dfb3d207f56d0f7d648fcdcd58493e4.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 10:52 UTC](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/20 "2020-07-23T10:52:15Z")

</div>

Glad it wasnt my work that caused your issue 🙂

Looking at your parameters values, you have `msg.payload` and `msg.payload.name`

That tells me your parameter data is NOT formatted correctly.

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

```

As your second parameter `GroupName` has a value of `msg.payload.name` that instantly tells me your TVP parameter `msg.payload` is incorrect.

Can you show me the format of the `msg.payload` BEFORE it enters the MSSQL node?

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