Node.js使用msnodesqlv8调用SQL Server表类型参数存储过程报错

核心问题 #

你当前的代码直接将JavaScript的table对象拼接到SQL字符串中，导致SQL Server无法识别这个对象为合法的表类型参数，触发类型转换错误（SQLSTATE 22018），同时还存在严重的SQL注入风险。

解决方案：使用参数化查询传递表类型参数 #

msnodesqlv8支持通过参数化方式正确传递SQL Server表类型参数，需要按以下步骤修改代码：

1. 修正表类型参数的定义（可选但更合理） #

你的存储过程中，插入customerMediaValue时使用的是新生成的客户ID（IDENT_CURRENT('customers')），而非传入的mediaValue表参数中的cId，因此可以去掉generateTable中多余的cId列：

const generateTable = () => {
  const table = {
    columns: [
      { name: "mediaId", type: "bigint" },
      { name: "mediaTitle", type: "nvarchar(300)" },
    ],
    rows: [[1, "test"]],
  };
  return table;
};

2. 重构存储过程调用代码，使用参数化查询 #

彻底放弃字符串拼接的方式，改用参数占位符?传递所有参数，其中表类型参数需要指定structured类型和对应的SQL Server表类型名称：

const addNewCustomer = async (con, req, resp) => {
  const {
    customerType,
    customerCad,
    phoneNumbers,
    email,
    fname,
    family,
    representative,
    link,
    birthday,
    occupation,
    financialLevel,
    gender,
    city,
    fullAddress,
    operatorId,
  } = req.body;

  try {
    const table = generateTable();
    // 参数化调用存储过程
    const result = await con.promises.query(
      `exec addCustomer 
        @customerType = ?, 
        @customerCad = ?, 
        @phoneNumbers = ?, 
        @email = ?, 
        @fname = ?, 
        @lname = ?, 
        @representative = ?, 
        @link = ?, 
        @birthday = ?, 
        @occupation = ?, 
        @financialLevel = ?, 
        @gender = ?, 
        @city = ?, 
        @fullAdress = ?, 
        @mediaValue = ?, 
        @operatorId = ?`,
      [
        customerType,
        customerCad,
        phoneNumbers,
        email,
        fname,
        family,
        representative,
        link,
        birthday,
        occupation,
        financialLevel,
        gender,
        city,
        fullAddress,
        // 表类型参数的特殊定义
        {
          type: "structured",
          name: "dbo.mediaType",
          value: table
        },
        operatorId
      ]
    );
    resp.status(200).send(result.results[0]);
  } catch (err) {
    resp.status(500).send({ msg: "服务器错误", err: JSON.stringify(err) });
  }
};

关键说明 #

• 参数化查询：避免了SQL注入风险，同时让msnodesqlv8驱动正确处理每个参数的类型转换，解决SQLSTATE 22018错误。
• 表类型参数传递：必须指定type: "structured"和SQL Server中定义的表类型全名dbo.mediaType，驱动才能将JavaScript的table对象正确映射为SQL Server的表类型参数。
• 冗余字段清理：去掉mediaValue中的cId列，避免数据逻辑冲突，因为存储过程中不会使用该字段的值。

额外建议 #

检查存储过程中的参数拼写：@fullAdress（存储过程参数）和fullAddress（Node.js变量）拼写不一致，虽然当前不影响功能，但建议统一拼写，避免后续维护时出现问题。

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