node.jssql-serverexpresstediousnode-mssql

Adding multiple inputs to query MS SQL Server instance using 'node-mssql' npm package

I would like to query MS SQL Server instance to serve a very simple POST request. I am using nodejs + express. The following code (async version) considers only first input types only and therefore returns error -

  let pool = await sql.connect(dbCredentials);
  let bookName = "string to be matched" ; 
  let ranksVal= 10
  
  let result = await pool
    .request()
    .input("item", sql.VarChar, bookName)
    .input("ranksVal", sql.Int , ranksVal)
    .query(`select top @ranksVal * from dbTable where book = @item order by counts desc`);

Ideally, the above code should return a result for the following SQL query :

 select top 10 * from dbTable where book = "string to be matched" order by counts desc

Alternatively, the following solution works :

  let result = await pool
    .request()
    .input("item", sql.VarChar, bookName)
    .query(`select top ${ranksVal} * from dbTable where book = @item order by counts desc`);

But I would like to understand how can we pass multiple values to req.input() method. Thank you.

Solution

  • To use an expression or a parameter in a TOP values clause you must use parentheses. eg

     .query(`select top (@ranksVal) * from dbTable where book = @item order by counts desc`);