Passing array with brackets to MSSQL

[noob69]

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.

[bakman2]

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

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

[E1cid]

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

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

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

[Steve-Mcl]

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

[noob69]

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