Node-mssql: Is there an example on how to use the WHERE field IN clause?

[rohithshenoyg]

Hi Patrik,

I'm trying to perform a simple select query as below:

select * from user_table where id in (@id)

I specify the id parameter as a string '1,2,3', but somehow SQL server fails to Convert this from nvarchar to int. I am relying on .input() function's capability of assigning a type, when it is unspecified.

However, when I do a query from SQL Server, the query select * from user_table where id in (1,2,3) works well.

An example on how to use the WHERE field IN clause would be very useful.

[patriksimek]

Unfortunately this is not possible. Please, see https://github.com/patriksimek/node-mssql/issues/313 for more details on that.

[Shockolate]

@rohithshenoyg , you could do a reduce on your array to construct the SQL string.
Something like:

const array = [1, 2, 3]
const sqlString = `SELECT * FROM user_table WHERE id IN (${array.reduce((prev, curr) => {
  return `${prev},${curr}`;
})})`;
request.query(sqlString);

[rohithshenoyg]

@Shockolate Thanks for the excellent pointer. I used your approach and expanded single parameter to multiple parameters and used request.input() in conjunction. Works great!

@patriksimek Thank you very much for the excellent library!! 👍

[jcollum]

Array.join is much simpler there:

$ node --harmony
> const array = [1, 2, 3]
undefined
> const str0 = `SELECT * FROM user_table WHERE id IN (${array.join(',')})`
undefined
> str0
'SELECT * FROM user_table WHERE id IN (1,2,3)'

[patriksimek]

Don't forget to sanitize you code to prevent SQL injections.

[Aides359]

@rohithshenoyg could you give an example of your solution (especially the part using request.input() in conjunction).

[m-adil]

@Shockolate In your solution, there's a closing parentheses missing at the end, the solution will be like:

const array = [1, 2, 3]
const sqlString = `SELECT * FROM user_table WHERE id IN (${array.reduce((prev, curr) => {
  return `${prev},${curr}`;
})})`;
request.query(sqlString);

However I'll surely prefer array.join() as suggested by @jcollum. But I'm confused of using req.input() as its not working at my end. here's what I'm trying:

const data = [1, 2, 3];
req.input('array', sql.Int, data.join(","));
const query = `SELECT * FROM tablename WHERE column IN (@array)`;

The query runs without any error but does show empty results which isn't the case using query without req.input(). Can any body please confirm if the problem is in data type or somewhere else? I second @Aides359 comment to please share an example especially with req.input();

[braco]

@m-adil did you solve this?

[m-adil]

Sorry @braco, it's almost an year old thing, that was the problem I faced while working on a very short term project and I didn't remember right now about the workaround I'd used in this regard. I'm gonna find that project code at my end and will let you know if i found it.

[m-adil]

@braco In my case, I'd written the query something like WHERE id IN ('+ ids.join(",") +') where ids was an array. Please test it out something like this and share your feedback.

[dimisus]

@m-adil @braco

Basically all the examples before use arrays with INT datatype. So whenever they call reduce or join their output is from [1, 2 ,3] to (1, 2, 3).

However when you work with strings and your query is to compare strings (or VarChars) you have to put every single value in quotation marks, thus your output has to be from [1, 2, 3] to ('1', '2', '3').

This is my current workaround:

queryData = ['A2054900727', 'A2054900827', 'A4638800626', 'A2928852600'];
let generateString = ''; 
queryData.map((el, i) => {
    if (i == queryData.length-1) return generateString += `'${el}'`;
    else return generateString += `'${el}',`;
});
const queryString = `SELECT * FROM SC10930 WHERE RTRIM(LTRIM(CODE)) IN (${generateString})`;
// SELECT * FROM SC10930 WHERE RTRIM(LTRIM(CODE)) IN ('A2054900727','A2054900827','A4638800626','A2928852600')

[goforgold]

For now, I've created a function which adds parameters dynamically like below. It is SQL Injection safe and can work for any type (no need to worry about quotes) as I've taken type as an argument.

Function:

/**
 * @param request sql request object
 * @param {string} columnName sql table column name
 * @param {string} paramNamePrefix prefix for parameter name
 * @param type parameter type
 * @param {Array<string>} values an array of values
 */
function parameteriseQueryForIn(request, columnName, parameterNamePrefix, type, values) {
  var parameterNames = [];
  for (var i = 0; i < values.length; i++) {
    var parameterName = parameterNamePrefix + i;
    request.input(parameterName, type, values[i]);
    parameterNames.push(`@${parameterName}`);
  }
  return `${columnName} IN (${parameterNames.join(',')})`
}

Usage:

var query = 'SELECT * FROM TableName WHERE ' + parameteriseQueryForIn(queryRequest, 'Name', 'nameParam', sql.NVarChar, ['Name1', 'Name2'])

I still wish if something like below should support, so programmers don't have to all this and also don't have to worry about sanitization to prevent injection.

var query = "SELECT * FROM myTable WHERE code IN (@datas)"

var param =  {
    name: 'datas',
    type: sql.VarChar,
    value: ['095578','095602']
};

request.input(param.name, param.type, param.value);

Thanks,

[dimisus]

Extremely needed feature ....

[dhensby]

I believe this feature was added in #593 ?

[dimisus]

I work with prepared statements... and I am frustrated.

I tried adding tvps as described in the docs... but it does not work... all prepared statements end with declaration of undefined

At the moment I do extremely awful code as a work around. The ps.input(array as table) is mandatory to work with WHERE IN

[dhensby]

I think you may need to open more specific issues with the problems you have

[christiaanwesterbeek]

Watch out with empty arrays! The rest of the query won't be part of the final sql statement anymore.

989