Nodejs query in SQL Server with Like

Question

I’m starting some routines with Node.js and sqlserver.

My Coldfusion routine is a materials query and calls the Node api via cfhttp.

I need to pass as a parameter to the API two or more fields to be filtered. I am testing first with a field and my select does not register properly because of the use of LIKE.

Look at my routine:

router.get('/materialNome/:material', (req, res) =>{
    let filter = '';
    if(req.params.material)filter= ' WHERE NOME_SERVICO LIKE' + '%' +(req.params.material)+'%';
    execSQLQuery('SELECT ID_MATERIAL,ID_FAMILIA,NOME_SERVICO FROM SERVICO.SERVICOSMATERIAL' + filter, res);
})

I don’t know how to use the like on Node.

Could someone exemplify?

Answer 1

You are not using the quotes correctly and will give problem. Follow correct example of concatenation using single and double quotes.

var nome = req.body.nome;
const query = "SELECT * FROM cadastros_gerais WHERE razao_social LIKE '%" + nome + "%'  AND ativo = 1";

Like needs quotes to indicate a string value, so you can use the 2 types of quotes, one to concatenate and the other to indicate the LIKE string.

Answer 2

Utilize Template String to more easily view the error in your query:

router.get('/materialNome/:material', (req, res) =>{
  let filter = '';
  if(req.params.material)filter= ` WHERE NOME_SERVICO LIKE '%${req.params.material}%'`;
  execSQLQuery(`SELECT ID_MATERIAL,ID_FAMILIA,NOME_SERVICO FROM SERVICO.SERVICOSMATERIAL ${filter}`, res);
})

---

Template String

Template literals are string literals that allow embedded expressions. You can use multi-line string and string interpolation with them. They were called "template strings" in versions prior to the ES2015 specification.

Syntax

`corpo de texto`
`texto linha 1
 texto linha 2`
`texto string ${expression} texto string`
tag `texto string ${expression} texto string`