Node.js中MSSQL预编译语句查询无结果问题求助

背景 #

原本通过字符串拼接IN条件的方式能正常从MSSQL查询到数据，但改用预编译语句后无法返回结果，尝试拆分SKU生成多参数预编译的方案也失败。

原有效字符串拼接代码 #

let products = await pool.request().query("SELECT TOP (1000) article " +
    "FROM [ProductCatalogue_brand].[dbo].[CatalogueItems] " +
    "where article in (" + skus + ")");
return products.recordsets;

注：传入的skus参数格式为 'sku1','sku2','sku3'

首次尝试预编译语句（无效） #

let pool = await sql.connect(config);
let products = await pool.request()
    .input('skus', sql.VarChar(8000), skus)
    .query("select article from [ProductCatalogue_brand].[dbo].[CatalogueItems] where Article in (@skus)");
return products.recordsets;

完整函数代码 #

var config = require('./dbconfig');
const sql = require('mssql');

//create a get product function form the database
async function getProducts(skus) {
    try {
        /*let pool = await sql.connect(config);
        let products = await pool.request()
            .input('skus', sql.VarChar(8000), skus)
            .query("select article from [ProductCatalogue_brand].[dbo].[CatalogueItems] where Article in (@skus)");
        return products.recordsets;*/

        /*let products = await pool.request().query("SELECT TOP (1000) article " +
            "FROM [ProductCatalogue_brand].[dbo].[CatalogueItems] " +
            "where article in (" + skus + ")");
        return products.recordsets;*/
    } catch (error) {
        console.log(error);
    }
}

//export getProducts function
module.exports = {
    getProducts: getProducts
}

尝试拆分SKU生成多参数预编译（无效） #

let pool = await sql.connect(config);
var ps = new sql.PreparedStatement(pool);
//convert skus to array
var skuArray = skus.split(",");
// Construct an object of parameters, using arbitrary keys
var paramsObj = skuArray.reduce((obj, val, idx) => {
    obj[`id${idx}`] = val;
    ps.input(`id${idx}`, sql.VarChar(200));
    return obj;
}, {});

console.log(Object.keys(paramsObj).map((o) => {return '@'+o}).join(','));

// Manually insert the params' arbitrary keys into the statement
var stmt = 'select article from [ProductCatalogue_brand].[dbo].[CatalogueItems] where Article in (' + Object.keys(paramsObj).map((o) => {return '@'+o}).join(',') + ')';
ps.prepare(stmt, function(err) {
    ps.execute(paramsObj, function(err, data) {
        console.log(data);
        ps.unprepare(function(err) {
        });
    });
});

问题原因分析 #

1. 首次预编译失败原因：
   把'sku1','sku2','sku3'作为单个字符串参数传入@skus时，SQL会将其识别为一个完整的字符串值，相当于执行WHERE Article = '''sku1'',''sku2'',''sku3'''，自然无法匹配数据库中的单个SKU值。

2. 拆分SKU后失败原因：

   • 拆分逻辑错误：原skus参数自带单引号，用split(",")拆分后得到的元素是["'sku1'", "'sku2'", "'sku3'"]，每个元素都带单引号，而预编译参数会自动添加引号转义，最终SQL中的参数值变成''sku1''，和数据库中的sku1不匹配。
   • 异步流程问题：使用回调方式调用ps.prepare和ps.execute，但函数是async类型，没有正确处理异步流程，可能函数已经返回但查询还未执行完成。

解决方案 #

方法1：正确拆分SKU并使用参数数组（推荐） #

先去掉SKU字符串中的单引号再拆分，然后逐个添加预编译参数：

async function getProducts(skus) {
    try {
        const pool = await sql.connect(config);
        // 去掉所有单引号后拆分SKU
        const skuArray = skus.replace(/'/g, '').split(',');
        
        // 生成SQL占位符和查询语句
        const placeholders = skuArray.map((_, idx) => `@sku${idx}`).join(',');
        const query = `SELECT article FROM [ProductCatalogue_brand].[dbo].[CatalogueItems] WHERE Article IN (${placeholders})`;
        
        const request = pool.request();
        // 逐个添加预编译参数
        skuArray.forEach((sku, idx) => {
            request.input(`sku${idx}`, sql.VarChar(200), sku);
        });
        
        const result = await request.query(query);
        return result.recordsets;
    } catch (error) {
        console.error(error);
        throw error; // 抛出错误让调用方处理
    }
}

方法2：使用表值参数（适合大量SKU场景） #

如果SKU数量较多，推荐使用SQL Server的表值参数：

1. 先在SQL Server中创建自定义表类型：

CREATE TYPE dbo.SkuList AS TABLE (Sku VARCHAR(200))

2. Node.js代码中使用表值参数：

async function getProducts(skus) {
    try {
        const pool = await sql.connect(config);
        const skuArray = skus.replace(/'/g, '').split(',');
        
        // 创建表值参数结构
        const tvp = new sql.Table();
        tvp.columns.add('Sku', sql.VarChar(200));
        skuArray.forEach(sku => tvp.rows.add(sku));
        
        const result = await pool.request()
            .input('SkuList', sql.TVP('dbo.SkuList'), tvp)
            .query(`SELECT article FROM [ProductCatalogue_brand].[dbo].[CatalogueItems] WHERE Article IN (SELECT Sku FROM @SkuList)`);
        
        return result.recordsets;
    } catch (error) {
        console.error(error);
        throw error;
    }
}

方法3：修复原有预编译语句的回调问题（不推荐） #

如果坚持使用PreparedStatement，需要将回调转为Promise，并修正SKU单引号问题：

async function getProducts(skus) {
    try {
        const pool = await sql.connect(config);
        const skuArray = skus.replace(/'/g, '').split(',');
        
        const ps = new sql.PreparedStatement(pool);
        const paramsObj = skuArray.reduce((obj, val, idx) => {
            const paramKey = `id${idx}`;
            obj[paramKey] = val;
            ps.input(paramKey, sql.VarChar(200));
            return obj;
        }, {});
        
        const placeholders = Object.keys(paramsObj).map(key => `@${key}`).join(',');
        const stmt = `SELECT article FROM [ProductCatalogue_brand].[dbo].[CatalogueItems] WHERE Article IN (${placeholders})`;
        
        // 将回调转为Promise处理异步流程
        const data = await new Promise((resolve, reject) => {
            ps.prepare(stmt, (err) => {
                if (err) return reject(err);
                ps.execute(paramsObj, (err, data) => {
                    if (err) return reject(err);
                    ps.unprepare((err) => {
                        if (err) return reject(err);
                        resolve(data);
                    });
                });
            });
        });
        
        return data.recordsets;
    } catch (error) {
        console.error(error);
        throw error;
    }
}

内容的提问来源于stack exchange，提问作者hkguile