如何使用Sequelize向MSSQL存储过程传递表值参数？请求具体实现示例

我之前帮同事处理过类似的需求，结合Sequelize和MSSQL的表值参数确实需要几个关键步骤，我给你整理了一套完整的可运行示例，一步步来：

1. 数据库端准备：创建表值类型 #

首先需要在MSSQL数据库中定义对应的表值类型，这是前提——因为表值参数依赖数据库预定义的类型。比如我们要传递一个包含用户ID和姓名的列表，执行以下SQL：

CREATE TYPE dbo.UserListType AS TABLE (
    UserId INT,
    UserName NVARCHAR(50) NOT NULL
);

同时创建对应的存储过程（假设我们用来批量处理用户数据）：

CREATE PROCEDURE dbo.BatchProcessUsers
    @Users dbo.UserListType READONLY -- 表值参数必须声明为READONLY
AS
BEGIN
    SET NOCOUNT ON;
    -- 这里写你的业务逻辑，比如插入/更新数据
    SELECT UserId, UserName FROM @Users; -- 示例：返回传入的参数数据
END

2. Sequelize基础配置 #

确保你已经安装了必要的依赖包：

npm install sequelize tedious
# TypeScript用户额外安装类型包
npm install -D @types/sequelize @types/tedious

然后初始化Sequelize连接：

const { Sequelize } = require('sequelize');
const { TableType, TableValue } = require('tedious'); // 引入tedious的表值相关工具

// 初始化数据库连接
const sequelize = new Sequelize('YourDatabaseName', 'YourUsername', 'YourPassword', {
  host: 'YourSqlServerHost',
  dialect: 'mssql',
  dialectOptions: {
    options: {
      encrypt: true, // Azure SQL需要开启，本地测试可根据情况调整
      trustServerCertificate: true, // 本地测试可跳过证书验证
      connectTimeout: 30000
    }
  }
});

// 测试连接（可选）
async function testConnection() {
  try {
    await sequelize.authenticate();
    console.log('数据库连接成功');
  } catch (err) {
    console.error('连接失败:', err);
  }
}
testConnection();

3. 构造表值参数 #

接下来我们需要把JavaScript数组转换成MSSQL能识别的表值参数对象：

// 模拟要传递的业务数据
const userBatchData = [
  { UserId: 101, UserName: 'Alice Smith' },
  { UserId: 102, UserName: 'Bob Johnson' },
  { UserId: 103, UserName: 'Charlie Davis' }
];

// 创建TableValue对象，对应数据库的表值类型
const tableValueParam = new TableValue();
userBatchData.forEach(user => {
  // 为每一行添加数据，注意字段类型要和数据库表值类型完全匹配
  tableValueParam.addRow({
    UserId: { value: user.UserId, type: 'Int' },
    UserName: { value: user.UserName, type: 'NVarChar', length: 50 }
  });
});

4. 调用存储过程并传递表值参数 #

使用Sequelize的query方法执行存储过程，这里需要手动指定参数类型为数据库的表值类型：

async function executeStoredProc() {
  try {
    const result = await sequelize.query(
      'EXEC dbo.BatchProcessUsers @Users = :userList', // 存储过程调用语句
      {
        replacements: {
          userList: {
            type: TableType('dbo.UserListType'), // 指定数据库中定义的表值类型名称
            value: tableValueParam
          }
        },
        type: Sequelize.QueryTypes.RAW // 根据存储过程返回结果选择合适的类型，RAW表示返回原始结果
      }
    );
    console.log('存储过程执行结果:', result[0]); // MSSQL的query结果通常是数组，第一个元素是数据集
  } catch (error) {
    console.error('执行存储过程出错:', error);
  } finally {
    await sequelize.close(); // 关闭连接
  }
}

// 执行调用
executeStoredProc();

关键注意事项 #

• 表值类型匹配：代码中指定的TableType('dbo.UserListType')必须和数据库中创建的类型完全一致，包括架构（比如dbo.前缀）。
• 字段类型匹配：添加行时指定的type要和数据库表值类型的列类型对应，比如Int对应数字，NVarChar对应字符串，长度也要匹配。
• READONLY声明：存储过程中的表值参数必须加上READONLY，否则MSSQL会报错。
• 依赖版本：确保sequelize和tedious的版本兼容，建议使用较新的稳定版本（比如sequelize@6.x，tedious@15.x）。

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