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

Question

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.

David Browne - Microsoft · Accepted Answer · 2021-01-24 21:57:30Z

2

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`);

answered Jan 24, 2021 at 21:57

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related