SQL search for multiple ids at once - Node/Express

Question

I'm using node express mssql for a query to the DB to retrieve some data. The data I'm trying to send is a comma delimited string. But the DB recognizes them as ints because they are ids. The issue is that my query is only recognizing the first integer and not anything afterward so, for example, it would only recognize 10 -> (10,11,12). But I need all three ids to be sent.

route.js

router.get('/', (req, res) => {
    connectPool.then(pool => {

let projectId = req.query.id
        let newAr = new Array();
        let cpnew = 0
        newAr = projectId.split(',')

        for(a in newAr){
            newAr[a] = parseInt(newAr[a])
        }

        console.log(newAr, 'logging new array')
        for(let i=0; i<newAr.length;i++){
            console.log(newAr, newAr[i], 'logging info')
            if(newAr.length - 1 == i){
                cpnew += newAr[i]
            } else if(i == 0){
                cpnew = newAr[i] + ','
            }
            else {
                cpnew += newAr[i] + ',' 
            }
        }

        let sqlString = ` 
        SELECT p.Name
        FROM Projects p with (nolock)
        WHERE p.ProjectsID IN ((@projectId))
        `

        return pool.request().input('projectId', sql.Int, cpnew).query(sqlString)
    }).then(result => {
        let rows = result.recordset[0]

        res.status(200).json(rows);
        sql.close();
    }).catch(err => {
        res.status(500).send({ message: err})
        sql.close();
    });
})

So basically what I'm doing right because I return the query is changing the comma delimited string -> 10,11,12 to an array with ints -> [10,11,12], then changing that to ints with commas in between 10,11,12. Then place that into the variable into my string. I might be way off here but I'm lost at this point. Thanks!

Sameer · Accepted Answer · 2019-04-05 05:20:18Z

1

DECLARE @ProjectID VARCHAR(100) = '1,3,2';
SELECT  p.Name
FROM Projects p 
WHERE p.ProjectsID IN  ( SELECT  value
      FROM    STRING_SPLIT(@ProjectID, ',') ); /*STRING_SPLIT Function 
                                                 available from 
                                                 MSSQL 2016 and above*/

edited Apr 5, 2019 at 5:20

answered May 18, 2018 at 3:56

Sameer

4014 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

SQL search for multiple ids at once - Node/Express

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related