Avoiding hitting SQL Server db multiple times via the `mssql` package in Node

Question

I am using the mssql package in my Node app to query our SQL Server database. One thing I need to do is look up records from a table that match the IDs I have stored from a separate query to a different SQL database.

Because our SQL Server database is large and always growing, rather than get ALL the records, currently I am doing this to pull in only the records that match on an ID from my array:

const sourceArr = [
  {id: 1, name: "Joe", notes_detail_id: 123}, 
  {id: 2, name: "Jane", notes_detail_id: 456}, 
  {id: 1, name: "Billy", notes_detail_id: 789}
];

const getMatchingRecords = async function() {
  const targetRecords = [];
  const query = `SELECT NoteDetailsId
  FROM SL_Empsheets
  WHERE NoteDetailsId = ${record.notes_detail_id}`;
    for (let record of sourceArr) {
      let matchingRecord = await sqlServerQueryHandler(query);
      if (matchingRecord) {
        targetRecords.push(matchingRecord);
      }
    }
    return targetRecords;
};

And FYI, my sqlServerQueryHandler() looks like this:

const sql = require('mssql');
const config = require('./../../../configuration/sql-server-config');

const pool = new sql.ConnectionPool(config);
const poolConnect = pool.connect();

pool.on('error', err => {
  console.log(err);
});

module.exports = async function sqlServerQueryHandler(query) {
  try {
    await poolConnect;
    const request = pool.request();
    const result = await request.query(query);
    return result;
  } catch (err) {
    console.error(err);
  }
};

My current implementation will work. However, this ends up hitting the database multiple times.

So, my question is, is there a way I do something similar via the mssql package, but where I can pass in an array of values, rather than having to iterate through the array and hit the database one record at a time?

What about a builded query ? Something like "SELECT NoteDetailsId FROM RP_Empsheets WHERE NoteDetailsId = firstId OR NoteDetailsId = secondId OR NoteDetailsId = thirdId (etcetera)" ? — Federico Provenziani
– Federico Provenziani, Commented Jan 30, 2020 at 15:32
I won't know exactly how many original records there will be to match to, as that will change over time, so I wouldn't be able to do this. — Muirik
– Muirik, Commented Jan 30, 2020 at 15:34
Of course i mean that "firstId", "secondId" ectetera are provided by your array. — Federico Provenziani
– Federico Provenziani, Commented Jan 30, 2020 at 15:35
What would that look like? Do you mean do that within the loop? — Muirik
– Muirik, Commented Jan 30, 2020 at 15:36
"I wouldn't be able to do this"...why not? Build up the SQL string gradually, by looping through your array and adding another section of the WHERE clause to the SQL each time. Probably neater to write it as an IN rather than a series of ORs — ADyson
– ADyson, Commented Jan 30, 2020 at 15:37

Muirik · Accepted Answer · 2020-01-30 15:59:26Z

1

At a glance it looks like your getMatchingRecords function may not be copied correctly, otherwise, i'm not sure where your initial variable record is coming from. I think you should be building the query inside the for loop rather than before it.

Assuming that is the case, you could use an IN statement in SQL.

SELECT * FROM table WHERE id IN (1,2,3)

You could write this in JS like so:

const getMatchingRecords = async function() {
    const queryIn = sourceArr.map(r => r.notes_detail_id);
    const query = `SELECT NoteDetailsId
                   FROM SL_Empsheets
                   WHERE NoteDetailsId IN (${queryIn})`;
    return sqlServerQueryHandler(query);
};

edited Jan 30, 2020 at 15:59

Muirik

6,34910 gold badges74 silver badges134 bronze badges

answered Jan 30, 2020 at 15:41

MGuilmet

2012 silver badges6 bronze badges

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

2 Comments

Muirik Over a year ago

I added some additional code above, to show you what the array looks like.

Muirik Over a year ago

Thanks! By the way, you don't need the join() on the first part. Just do const queryIn = sourceArr.map(r => r.notes_detail_id);

Collectives™ on Stack Overflow

Avoiding hitting SQL Server db multiple times via the `mssql` package in Node

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related