# Passing array with brackets to MSSQL

**URL:** <https://discourse.nodered.org/t/passing-array-with-brackets-to-mssql/63094>\
**Category:** General\
**Created:** [26 May 2022 08:37 UTC](https://discourse.nodered.org/t/passing-array-with-brackets-to-mssql/63094 "2022-05-26T08:37:46Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![noob69](https://avatars.discourse-cdn.com/v4/letter/n/0ea827/32.png) [@noob69](https://discourse.nodered.org/u/noob69)\
**Post date:** [26 May 2022 08:37 UTC](https://discourse.nodered.org/t/passing-array-with-brackets-to-mssql/63094/1 "2022-05-26T08:37:46Z")

</div>

Hi,  
I want run following query using mssql node but I want to pass some custom values for the id. msg.id looks as follows.

`(2,9)`

`SELECT * FROM [dbo].[alert] WHERE id IN (1,2,3,4,5)`

So I've tried it using parameters @id as follows.

`SELECT * FROM [dbo].[alert] WHERE id IN @id`

and also I've tried as the documentation.

`SELECT * FROM [dbo].[alert] WHERE id IN {{{msg.id}}}`

But non of the above methods give me the output.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [26 May 2022 09:06 UTC](https://discourse.nodered.org/t/passing-array-with-brackets-to-mssql/63094/2 "2022-05-26T09:06:13Z")

</div>

reading the docs, it appears that the node drops `msg` from the handlebars, did you try:

```auto
SELECT * FROM [dbo].[alert] WHERE id IN {{{id}}}

```

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [26 May 2022 09:38 UTC](https://discourse.nodered.org/t/passing-array-with-brackets-to-mssql/63094/3 "2022-05-26T09:38:41Z")

</div>

As bakman2 says you need to drop the `msg.`  
But if you want the brackets add them to template.  
Here is example

```auto
[{"id":"65f7b5c6.940324","type":"inject","z":"efbbf209f839382a","name":"","props":[{"p":"id","v":"[1,2,3,4]","vt":"json"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":120,"y":880,"wires":[["f457b018.1681c"]]},{"id":"f457b018.1681c","type":"template","z":"efbbf209f839382a","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT * FROM [dbo].[alert] WHERE id IN ({{{id}}})","output":"str","x":350,"y":860,"wires":[["72dc466d.4afbb"]]},{"id":"72dc466d.4afbb","type":"debug","z":"efbbf209f839382a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":640,"y":880,"wires":[]}]

```

```auto
SELECT * FROM [dbo].[alert] WHERE id IN ({{{id}}})

```

---

<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:** [26 May 2022 09:50 UTC](https://discourse.nodered.org/t/passing-array-with-brackets-to-mssql/63094/4 "2022-05-26T09:50:53Z")

</div>

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

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

```sql
SELECT * FROM [dbo].[alert]
WHERE id IN (select value from STRING_SPLIT(@IDs,','))

```

NOTE: This expects `msg.id` to be a CSV string e.g. `4,5,6`

---

<div class="post-metadata">

**Author:** ![noob69](https://avatars.discourse-cdn.com/v4/letter/n/0ea827/32.png) [@noob69](https://discourse.nodered.org/u/noob69)\
**Post date:** [26 May 2022 10:33 UTC](https://discourse.nodered.org/t/passing-array-with-brackets-to-mssql/63094/5 "2022-05-26T10:33:12Z")

</div>

Thanks all for the amazing explanation @Steve-Mcl @E1cid@bakman2

---

<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:** [9 June 2022 10:33 UTC](https://discourse.nodered.org/t/passing-array-with-brackets-to-mssql/63094/6 "2022-06-09T10:33:52Z")

</div>

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