Bind by SQL Server position using JS Node (MSSQL)

Question

I wonder if it is possible to bind the parameters by position instead of the name using mssql in Nodejs.

Ex.:

//Por nome
return pool.request()
.input('input_parameter1', sql.Int, 10)
.input('input_parameter2', sql.Int, 20)
.query('select * from mytable where id = @input_parameter1 and valor = @input_parameter2')

What I need is something like:

//Por posição
return pool.request()
.query('select * from mytable where id = ? and valor = ?', [10, 20], 
function(err, result){
console.log("Resultado: " + result);
});

Answer 1

You can use it that way with the mysql (link). The idea is how you show in code, use markers ? in the query and then pass an array with the positions whose value should be used instead of the marker.

According to the example of the documentation:

connection.query('UPDATE users SET foo=?, bar=?, baz=? WHERE id=?', ['a', 'b', 'c', userId], (error, results, fields) => {
  if (error) throw error;
  // ...
});

To implement this in mssql you could make your own wrapper:

const superQuery = (conn, query, values) => {
  let req = pool.request();
  let value = values.shift();
  let counter = 1;

  while (typeof value !== 'undefined') {
    let sep = 'input_parameter' + (counter++);
    req = req.input(sep, value);
    query = query.replace('?', '@' + sep);
    value = values.shift()
  }
  return req.query(query);
}

And then wear it like this:

superQuery(
    connection, 
    'UPDATE users SET foo=?, bar=?, baz=? WHERE id=?', 
    ['a', 'b', 'c', userId]
).then(res => {
    ...etc
});

Note: the mssql has a concept of prototypes as the second optional argument of . input(). It would be simple to incorporate this into my wrapper so as not to lose this level of security. However the array passed instead of simple would be an object with the prototype as well.