Handling passing variable with template string where value is out of scope

Question

I have a function in my Node app, that makes use of the mssql package, to run some SQL updates. What I want to be able to do is pass in a variable for the actual update query/value, so that I can re-use the function. But I'm running into a problem where the value I'm passing in is undefined because of the scope.

To be clear, it will work if I do this:

  if (recordsToUpdate.length > 0 && sourceRecords.length > 0) {
    for (let record of recordsToUpdate) {
      for (let source of sourceRecords) {
        if (record.NoteDetailsId === source.notes_detail_id) {
          console.log('source.category: ', source.category);
          const updateQuery = `
            UPDATE Emp_Worksheets
            SET Category = '${source.category}';
          `;
          await sqlServerQueryHandler(updateQuery);
        }
      }
    }
  }

But if I move the updateQuery outside the function I get a 'source' is not defined error:

  let updateQuery = `
    UPDATE Emp_Worksheets
    SET Category = '${source.category}';
  `;

  if (recordsToUpdate.length > 0 && sourceRecords.length > 0) {
    for (let record of recordsToUpdate) {
      for (let source of sourceRecords) {
        if (record.NoteDetailsId === source.notes_detail_id) {
          console.log('source.category: ', source.category);
          await sqlServerQueryHandler(updateQuery);
        }
      }
    }
  }

How can I get around this? The error is on this line:

SET Category = '${source.category}';

Is there a way I can pass a default value instead? Obviously, source is available when the function runs, but I'm getting the error because source within the original declared variable doesn't yet exist. Perhaps there's an obvious way of handling this, but it is escaping me.

Well, it's not a constant, as its value depends on the `source`. Keep it in the scope where that variable is available, or put it in a function. — Bergi, Jan 31 '20 at 14:14

Oron Bendavid · Accepted Answer · 2020-01-31T14:45:45.720

2

You can create a generic function and pass 3 parameters:

const updateQuery = (table, column, value) =>{
  return `UPDATE ${table} SET ${column} = ${value}`;
}

In order to avoid sql-injection you need to use Parameterized query:

One syntax for example:

UPDATE ${table} SET ${column} = ?, [${value}]

You can find more info about it here

edited Jan 31 '20 at 14:45

answered Jan 31 '20 at 14:15

Oron Bendavid

1,485
3
18
34

Returning a function is a good idea. But this `return UPDATE ${table} SET ${column} = ?, [${value}]` produces a syntax error. Also, this is an in-house tech team app. No public access, behind a firewall. – Muirik Jan 31 '20 at 14:41
Hi, incase you succeeded with the 1st example, you need to check the right syntax for the parametriez query according to your db engine. You can specify what db you are using and I will help you with that too. – Oron Bendavid Jan 31 '20 at 14:44
I am writing to a SQL Server database. – Muirik Jan 31 '20 at 15:01
So I guess that you are using https://www.npmjs.com/package/mssql – Oron Bendavid Jan 31 '20 at 15:15
That's correct, yes. I mentioned that in the question. – Muirik Jan 31 '20 at 16:11
Great. So it syntax for parameters is: ... .input('input_parameter', sql.Int, value) .query('select * from mytable where id = @input_parameter') ... – Oron Bendavid Jan 31 '20 at 16:20

Handling passing variable with template string where value is out of scope

1 Answers1