How to Handle Passing in String Values via `mssql` package in Node Project

Question

I am using the mssql package in my Node project to handle querying for data in my SQL Server db. I am running into a challenge with this particular query:

const idArr = ['A123', 'B456'];

SELECT DOCNO
FROM dbo.SA30301
WHERE DOCNO
IN (${idArr})

The problem is that, with this syntax, the values will not be passed in as strings, even though the array contains string values. So with this implementation I get an invalid column name error.

I've also tried this:

SELECT DOCNO
FROM dbo.SA30301
WHERE DOCNO
IN ('${idArr}')

... and while this doesn't error out, it doesn't return any records because basically both values are passed in as one long string.

Also, FYI, the type for DOCNO in SQL Server is char(17).

So my question is, how can I make sure these values get evaluated as individual string values?

My other option may be to use a for-loop and go one by one, but that will result in many hits to the db rather than just one.

Muirik · Accepted Answer · 2020-03-25 17:58:28Z

1

Try with a Javascript's join function

SELECT DOCNO
FROM dbo.SA30301
WHERE DOCNO
IN (${idArr.map(item => `'${item}'`).join(',')})

You may need to escape the quotes.

edited Mar 25, 2020 at 17:58

Muirik

6,34910 gold badges74 silver badges134 bronze badges

answered Mar 25, 2020 at 17:41

samdouble

4844 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

samdouble Over a year ago

And I would recommend editing your question with an example of what you mean by "individual strings"

samdouble Over a year ago

I've edited my answer. The quotes before and after every item will make it so that you will get "A123","B456"

Muirik Over a year ago

Nice one! I made one small edit to your answer, because in SQL Server those need to be single quotes, not double. But other than that this works perfectly!

Collectives™ on Stack Overflow

How to Handle Passing in String Values via `mssql` package in Node Project

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related