How to Pass Parameter to Mssql Query in Node Js

Passing input parameters to node mssql query function

You can pass the value of req.params.workOrderId into your async function and then use that value inside. check the following code.

app.get("/api/test/:workOrderId", function(req, res) {
console.log(req.params.workOrderId);

(async function(workOrderId) {
        const pool = new sql.ConnectionPool(dbConfig)
        pool.on('error', err => {
            console.log('sql errors', err);
        });

        try {
            await pool.connect();
            let result = await pool.request()
              .input('input_parameter', sql.VarChar(50), workOrderId)
              .query('SELECT * FROM [Quotation] WHERE [idWorkOrder]= @input_parameter');

              console.log(result);

            res.send(result.recordset);
            return {success: result}
         } catch (err) {
             return {err: err};
         } finally {
          console.log('request complete')
          pool.close();    // closing connection after request is finished
    }
  })(req.params.workOrderId); // <===pass value to the function

})

Trying to pass the parameter in a query in Node.js mssql

First

var data = await sql.query`SELECT * FROM mytable WHERE ${types}`;

Is missing parenthesis, so it isn't actually calling .query, but you really should be doing the second method anyways, for security (to prevent sql injection).

But the second way is probably throwing an error the way it is -

In sending a prepared statement, the sql has to be interpreted/understood without the @types literal being given - it considers it a parameter. select * from x where 'hello world' isn't valid sql, and everything within @types is being bound as a Varchar literal.

do

SELECT * FROM dbo.denormalized WHERE ContractType = @types

and make the javascript types variable only contain AllRisks. Note you will not need to escape the quotes around AllRisks, the value "AllRisks" as a string should be sufficient; e.g. let types = 'AllRisks'. You already told the library you were binding a Varchar.

--- Edit

Since you want to do an array of types, I looked into doing WHERE IN using the mssql package and turned up this related question - NodeJS MSSQL WHERE IN Prepared SQL Statement

I would throw in the towel on using the mssql module directly at this point and use http://knexjs.org/ which is common, and uses mssql underneath the hood. It will handle this sort of thing for you with something like knex.select().from('table').whereIn('ContractTypes', types).

Insert request parameters using node js and mssql

You're accessing the req.body.CName directly in the string, this won't work, you'll need to use parameters for your query:

// Change execute query to accept parameters.
var executeQuery = function(res,query,parameters){
    sql.connect(dbconfig,function(err){
        if(err){
            console.log("there is a database connection error -> "+err);
            res.send(err);
        }
        else{
            // create request object
            var request = new sql.Request();

            // Add parameters
            parameters.forEach(function(p) {
                request.input(p.name, p.sqltype, p.value);
            });

            // query to the database
            request.query(query,function(err,result){
                if(err){
                    console.log("error while querying database -> "+err);
                    res.send(err);
                }
                else{
                    res.send(result);
                    sql.close();
                }
            });
        }
    });
}



//POST API
app.post("/api/Category", function(req , res){

    var parameters = [
      { name: 'CName', sqltype: sql.NVarChar, value: req.body.CName},
      { name: 'CSubCategory', sqltype: sql.NVarChar,  value: req.body.CSubCategory},
    ];

    var query = "INSERT INTO [Category] (CName,CSubCategory) VALUES (@CName, @CSubCategory)";
    executeQuery (res, query, parameters);
});

How to pass date as an input to SQL Server query in node

Okay, a huge oversight on my part and sincere apologies to those who tried to find an answer. I found out that I was running the query() method on the wrong object. I was running the query method on db object whereas I should have run it on the request object. The code works perfectly after calling the method on the right object.

MSSQL for Node: How to use LIKE with an input parameter in the query?

Have you tried prefixing and suffixing the contents of criteria with the %?

e.g.

new sql.ConnectionPool(db).connect().then(pool => {
return pool.request()
.input('input_parameter', '%'+criteria+'%')
.query(query)
}).then(result =>
   ...etc

How to pass parameters to mysql query callback in nodejs

If you are using node-mysql, do it like the docs say:

connection.query(
    'SELECT * FROM table WHERE id=? LIMIT ?, 5',[ user_id, start ], 
    function (err, results) {

    }
);

The docs also have code for proper escaping of strings, but using the array in the query call automatically does the escaping for you.

https://github.com/felixge/node-mysql

Building a kitchen sink query. Error passing @ parameters to SQL Server

Without a query builder library (e.g. Knex), you'll need to

• form the SQL query (as a string)
• put the parameters into place

e.g. something like this:

const whereClauses = [];
const inputs = {}; // map input name -> [type, value]

// (1) Process data into WHERE clauses and inputs

if (data.basics.memberId) {
  whereClauses.push(`memberid=@memberId`);
  inputs.memberId = [utils.sql.Int, data.basics.memberId];
}

if (data.basics.somethingElse) {
  whereClauses.push(`somethingElse=@somethingElse`);
  inputs.somethingElse = [utils.sql.Int, data.basics.somethingElse];
}

// (etc..., you could use a loop or something for the above)

// (2) Form the final SQL query

const sqlStringBits = ["SELECT * FROM ... WHERE "];

for (let whereClause of whereClauses) {
  sqlStringBits.push(whereClause);
  sqlStringBits.push("AND");
}
if (whereClauses.length) {
  sqlStringBits.pop(); // Remove final AND if we had added one
}

const sqlString = sqlStringBits.join(" ");

// (3) Form the `request` and put the inputs into place

const pool = await utils.poolPromise;
let request = pool.request();
for (let inputName in inputs) {
  request = request.input(inputName, ...inputs[inputName]);
}

// (4) Use the request (endowed with inputs) with the query
const recordSet = await request.query(sqlString);

// (5) Do something with the record set!

---

Related Topics