Node.js调用SQL存储过程传递TVP时列值交换问题求助

在Node.js中使用mssql/msnodesqlv8调用SQL存储过程并传递表值参数（TVP）时，存储过程读取到的两个特定列的值发生了交换。

API端点代码 #

const sql = require("mssql/msnodesqlv8");
const dataAccess = require("../DataAccess");

const fn_CreateProd = async function (product) {
  let errmsg = "";
  let objBlankTableStru = {};
  let connPool = null;
  await sql
    .connect(global.config)
    .then((pool) => {
      global.connPool = pool;
      
      productsStru = pool.request().query("select * from products where 1=2");

      return productsStru;
    })
    .then(productsStru=>{
      objBlankTableStru.products = productsStru

      productsOhStru = global.connPool.request().query("select * from products_oh where 1=2");
      
      return productsOhStru
    })
    .then((productsOhStru) => {
      objBlankTableStru.products_oh = productsOhStru
      
      let objTvpArr = [
        {
          uploadTableStru: objBlankTableStru,
        },
        {
          tableName : "products",
          tvpName: "tvp_products",
          tvpPara: "tblProds"
        },
        {
          tableName : "products_oh",
          tvpName: "tvp_product_oh",
          tvpPara: "tblProdsOh",
        }        
      ];

      newResult = dataAccess.getPostResult(
        objTvpArr,
        "sp3s_ins_products_tvp",
        product
      );

      console.log("Result of Execute Final procedure", newResult);
      return newResult;
    })
    .then((result) => {
      // console.log("Result of proc", result);
      if (!result.recordset[0].errmsg)
        errmsg = "Products Inserted successfully";
      else errmsg = result.recordset[0].errmsg;
    })
    .catch((err) => {
      console.log("Enter catch of Posting prod", err.message);
      errmsg = err.message;
      if (errmsg == "") {
        errmsg = "Unknown error from Server... ";
      }
    })
    .finally((resp) => {
      sql.close();
    });
  return { retStatus: errmsg };
};

module.exports = fn_CreateProd;

Getpostresult函数代码 #

const getPostResult = (tvpNamesArr, procName, sourceData,singleTableData) => {
  let arrtvpNamesPara = [];
  let prdTable = null;
  let newSrcData = [];

  let uploadTable = tvpNamesArr[0];

  for (i = 1; i <= tvpNamesArr.length - 1; i++) {
    let tvpName = tvpNamesArr[i].tvpName;
    let tvpNamePara = tvpNamesArr[i].tvpPara;
    let TableName = tvpNamesArr[i].tableName

    let srcTable = uploadTable.uploadTableStru[TableName]
    srcTable = srcTable.recordset.toTable(tvpName);

    let newsrcTable = Array.from(srcTable.columns);

    newsrcTable = newsrcTable.map((i) => {
      i.name = i.name.toUpperCase();
      return i;
    });
    if (!singleTableData)
        newSrcData = sourceData.filter(obj=>{
            return (obj.tablename.toUpperCase()===TableName.toUpperCase())
        })
    else 
    {
        newSrcData = sourceData
    }    

    console.log(`Filtered Source data for Table:${TableName}`,newSrcData)
    prdTable = generateTable(newsrcTable, newSrcData, tvpName);

    arrtvpNamesPara.push({ name: tvpNamePara, value: prdTable });
  }

  const newResult = execute(procName, arrtvpNamesPara);
  return newResult;
};

问题原因分析 #

1. 列顺序不匹配：SQL Server的表值参数（TVP）是按列顺序而非列名匹配数据的。如果通过select *获取的空表结构列顺序，和SQL中定义的TVP类型列顺序不一致，会直接导致值错位。
2. generateTable函数逻辑缺陷：如果该函数是按数组索引顺序给TVP赋值，而非根据列名映射，当数据源字段顺序和TVP列顺序不同时，就会出现列值交换。
3. 异步代码未正确等待：原代码中.then链里的query调用未加await，导致productsStru和productsOhStru实际是Promise对象，后续处理会拿到错误的表结构。

解决方案 #

1. 确保列顺序完全一致 #

不要使用select *获取空表结构，而是显式指定列名，保证和TVP类型的列顺序完全匹配：

-- 替换select *为显式列名，顺序与tvp_products定义一致
select ColA, ColB, ColC from products where 1=2

2. 修正generateTable函数逻辑 #

确保函数根据列名映射数据，而非按顺序赋值。示例实现：

function generateTable(columns, data, tvpName) {
  const table = new sql.Table(tvpName);
  
  // 按TVP的列顺序添加列
  columns.forEach(col => {
    table.columns.add(col.name, sql[col.type]);
  });
  
  // 遍历数据源，按列名取值填入TVP行
  data.forEach(row => {
    const rowValues = columns.map(col => {
      // 兼容大小写不同的字段名
      return row[col.name] || row[col.name.toLowerCase()] || row[col.name.toUpperCase()];
    });
    table.rows.add(...rowValues);
  });
  
  return table;
}

3. 修复异步代码的等待问题 #

在.then链中调用query时添加await，确保拿到实际的查询结果：

// 第一个then块中
productsStru = await pool.request().query("select ColA, ColB, ColC from products where 1=2");

// 第二个then块中
productsOhStru = await global.connPool.request().query("select ColX, ColY from products_oh where 1=2");

4. 避免全局连接池的不当使用 #

移除global.connPool，直接使用pool对象传递，避免线程安全问题：

.then(async (pool) => {
  const productsStru = await pool.request().query("select ...");
  const productsOhStru = await pool.request().query("select ...");
  objBlankTableStru.products = productsStru;
  objBlankTableStru.products_oh = productsOhStru;
  
  // 后续逻辑...
})

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