How to pass sql file into node-mssql

Question

How do we pass external sql file into node-mssql query

I tried to pass in file as shown in below code but its not working. I tried concatenating string still it did not work

request.query(query + `IN ('${serverName.join("','")}')`)

const sql = require('mssql')
const config = require('../Util/config')
const fs = require('fs')
const log = require('../Util/log')

module.exports = function (req, res, viewFile, next) {
  log(req)

  const query = fs.readFileSync('./sql/Local-admin-user.sql', 'utf8')
  // console.log(query);

  const serverName = "'" + req.body.server_name.split(/\r?\n/).join("','") + "'"
  console.log(serverName)

  var dbConn = new sql.ConnectionPool(config)
  dbConn.connect().then(function () {
    var request = new sql.Request(dbConn)
    request.query(query)
      .then(output => {
        let dataSet = output.recordset
          console.log(dataSet)
        dbConn.close()
      })
      .catch(err => {
        let dataSet = err
        console.log('error from routes.js', dataSet)
       dbConn.close()
      })
  }).catch(function (err) {
    console.log(err)
  })
}

Local-admin-user.sql

select servername,
username,
groupname from GROUP_MEMBERS_DATA where servername in (@servers)

I am getting error like

Incorrect syntax near servername

Answer 1

I was able to resolve it like this

--remove "in (@servers)" from Local-admin-user.sql
--Example
select servername, username, groupname 
from GROUP_MEMBERS_DATA where servername

in javascript add below code

 const serverName = "'" + req.body.server_name.split(/\r?\n/).join("','") + "'"
 const sqlquery = (query + ` IN (${serverName})`)

So output of sqlquery will be as below

select servername, username, groupname 
from GROUP_MEMBERS_DATA where servername in ('server1','server2')