nodejs中写sql语句如何处理参数缺省情况？

Question

问题遇到的现象和发生背景

用nodejs做后台，前端传参来作条件查询。就像这样

let AssetNameQ = req.body.AssetNameQ === undefined ? '' : req.body.AssetNameQ
        let MeasureTypeQ = req.body.MeasureTypeQ === undefined ? '0 or 1=1' : req.body.MeasureTypeQ
        console.log(MeasureTypeQ, typeof MeasureTypeQ)
        sql.query`select *
                  from TB_Asset
                  where AssetName like '%' + ${AssetNameQ} + '%'
                    and (MeasureType = ${MeasureTypeQ})
                  order by OperationTime desc`.then(result => {
            console.log('成功查询辣')
            res.send(result)
        }).catch(err => console.log('失败啦', err))

报错为

"Conversion failed when converting the nvarchar value '0 or 1=1' to data type tinyint."

我想要的是，譬如前端这里查询两个条件，只填了其中一个条件a时，另一个b为空（可以为“”空字符串，也可以为undefined，后端判断下就行）查询语句要如何将条件b给忽略掉呢？因为这里是用的字符串拼接，所以似乎无论如何都只能自动变为nvchar类型，然后转换数值型失败，查询失败。

请问该怎么处理呢？

Answer 1

1、一般这种都是有查询条件就添加该列的条件，没有就不添加

let AssetNameQ = req.body.AssetNameQ === undefined ? '' : ( ' AND AssetName like ''%' + req.body.AssetNameQ + '%''' )
let MeasureTypeQ = req.body.MeasureTypeQ === undefined ? '' : ( ' AND MeasureType = ' + req.body.MeasureTypeQ )
console.log(MeasureTypeQ, typeof MeasureTypeQ)
sql.query`select *
            from TB_Asset
           where 1 = 1 ' + ${AssetNameQ} + ${MeasureTypeQ}
           order by OperationTime desc`.then(result => {
    console.log('成功查询辣')
    res.send(result)
}).catch(err => console.log('失败啦', err))