Node.js使用SQL Server表值参数时出现参数不存在错误，求解决

错误原因 #

你遇到的"The procedure 'sp_executesql' has no parameter named '@UserIds'"错误，核心原因是SQL Server需要先创建对应的用户自定义表类型，才能在T-SQL中使用表值参数。当前代码只在Node.js端定义了TVP结构，但数据库端没有匹配的类型，导致参数无法被识别。

解决方案 #

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

执行以下SQL语句，在目标数据库中创建与TVP结构匹配的表类型：

CREATE TYPE UserIdsTableType AS TABLE (
    UserId INT
);
GO

2. 修改Node.js代码中的TVP定义 #

创建sql.Table实例时，必须指定typeName属性，关联刚才创建的数据库表类型。修改后的TVP代码如下：

// 创建用户ID的表值参数
const tvp = new sql.Table();
tvp.columns.add('UserId', sql.Int);
// 关联数据库中对应的自定义表类型
tvp.typeName = 'UserIdsTableType';

// 填充用户ID到TVP
users.forEach((userId) => {
  tvp.rows.add(userId);
});

3. 优化查询逻辑（可选） #

原查询的HAVING条件可简化，确保逻辑更清晰：

SELECT g.Id AS GroupId
FROM ${groupTable} AS g
JOIN ${groupMembersTable} AS gm ON g.Id = gm.GroupId
-- 改用INNER JOIN，只匹配传入的用户ID
INNER JOIN @UserIds AS u ON gm.UserId = u.UserId
GROUP BY g.Id
HAVING 
  -- 组内成员数量与传入的用户数量完全一致
  COUNT(DISTINCT gm.UserId) = (SELECT COUNT(*) FROM @UserIds)

额外性能优化 #

原代码循环插入组成员的效率较低，可改用TVP批量插入提升性能：

// 创建组成员的TVP
const membersTvp = new sql.Table();
membersTvp.columns.add('GroupId', sql.Int);
membersTvp.columns.add('UserId', sql.Int);
membersTvp.columns.add('JoinedDate', sql.DateTime);
membersTvp.columns.add('LeftDate', sql.DateTime);
// 需提前在数据库创建对应的表类型
membersTvp.typeName = 'GroupMembersTableType';

users.forEach(userId => {
  membersTvp.rows.add(newGroup.recordsets[0][0].Id, userId, new Date(), null);
});

// 批量插入组成员
await connection.request()
  .input('Members', sql.TVP, membersTvp)
  .query(`
    INSERT INTO ${groupMembersTable} (GroupId, UserId, JoinedDate, LeftDate)
    SELECT GroupId, UserId, JoinedDate, LeftDate FROM @Members
  `);

修正后的核心代码片段 #

async function createOrGetGroup(group: any, users: number[]) {
  try {
    let connection = await sql.connect(config);
    // 创建用户ID的表值参数
    const tvp = new sql.Table();
    tvp.columns.add('UserId', sql.Int);
    tvp.typeName = 'UserIdsTableType';

    // 填充用户ID到TVP
    users.forEach((userId) => {
      tvp.rows.add(userId);
    });

    // 查询是否存在匹配的组
    const existingGroup = await connection.request()
      .input('UserIds', sql.TVP, tvp )
      .query(`
        SELECT g.Id AS GroupId
        FROM ${groupTable} AS g
        JOIN ${groupMembersTable} AS gm ON g.Id = gm.GroupId
        INNER JOIN @UserIds AS u ON gm.UserId = u.UserId
        GROUP BY g.Id
        HAVING COUNT(DISTINCT gm.UserId) = (SELECT COUNT(*) FROM @UserIds)
      `);

    if (existingGroup.recordsets[0].length > 0) {
      await connection.close();
      return existingGroup.recordsets[0][0].GroupId;
    } else {
      // 创建新组
      const newGroup = await connection.request()
        .input('name', sql.NVarChar, group.name)
        .input('teams', sql.Int, group.teams)
        .query(`
          INSERT INTO ${groupTable} ([name], teams)
          OUTPUT INSERTED.Id
          VALUES (@name, @teams)
        `);

      // 批量插入组成员
      const membersTvp = new sql.Table();
      membersTvp.columns.add('GroupId', sql.Int);
      membersTvp.columns.add('UserId', sql.Int);
      membersTvp.columns.add('JoinedDate', sql.DateTime);
      membersTvp.columns.add('LeftDate', sql.DateTime);
      membersTvp.typeName = 'GroupMembersTableType';

      users.forEach(userId => {
        membersTvp.rows.add(newGroup.recordsets[0][0].Id, userId, new Date(), null);
      });

      await connection.request()
        .input('Members', sql.TVP, membersTvp)
        .query(`
          INSERT INTO ${groupMembersTable} (GroupId, UserId, JoinedDate, LeftDate)
          SELECT GroupId, UserId, JoinedDate, LeftDate FROM @Members
        `);

      await connection.close();
      return newGroup.recordsets[0][0].Id;
    }
  } catch (error) {
    throw error;
  }
}

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