如何向SQL存储过程传递复杂参数（对象数组类型）

表值参数（TVP）方案 #

实现步骤 #

1. 先创建匹配Metadata结构的用户定义表类型：

   CREATE TYPE dbo.MetadataType AS TABLE (
       [Key] VARCHAR(50) CHECK ([Key] IN ('FileNumber', 'Id', 'Year', 'Month')),
       Value VARCHAR(255) NOT NULL
   );
   

   （注意Key是SQL关键字，要用方括号转义）
2. 编写接收参数的存储过程：

   CREATE PROCEDURE dbo.ProcessFileRequest
       @Action VARCHAR(50),
       @FileType VARCHAR(50),
       @Metadata dbo.MetadataType READONLY
   AS
   BEGIN
       SET NOCOUNT ON;
   
       -- 示例：根据Metadata过滤数据
       SELECT * 
       FROM YourTargetTable
       WHERE 
           EXISTS (
               SELECT 1 FROM @Metadata m
               WHERE 
                   CASE m.[Key]
                       WHEN 'FileNumber' THEN YourTargetTable.FileNumber
                       WHEN 'Id' THEN CAST(YourTargetTable.Id AS VARCHAR(255))
                       WHEN 'Year' THEN CAST(YourTargetTable.Year AS VARCHAR(255))
                       WHEN 'Month' THEN CAST(YourTargetTable.Month AS VARCHAR(255))
                   END = m.Value
           );
   
       -- 根据@Action执行对应逻辑（比如插入/更新/删除）
       IF @Action = 'INSERT'
       BEGIN
           -- 插入逻辑
       END
       ELSE IF @Action = 'QUERY'
       BEGIN
           -- 查询逻辑
       END
   END
   

优缺点 #

• 优势：
  • 强类型校验：表类型的CHECK约束直接限制Key的合法值，参数传入时自动校验，不用在存储过程里写额外判断。
  • 性能更优：SQL Server对TVP的处理效率高，尤其是大数据量的数组，能直接作为表变量参与JOIN、WHERE等操作，无解析开销。
  • 逻辑清晰：存储过程内直接操作表变量，代码可读性强，后续维护方便。
• 劣势：
  • 需要提前创建用户定义表类型，部署时多一步操作。
  • 应用层传递参数时，要构造对应的数据表结构（比如.NET用DataTable，Java用SQLServerDataTable），比传JSON稍繁琐。

JSON字符串方案 #

实现步骤 #

直接在存储过程里接收JSON字符串，解析后使用：

CREATE PROCEDURE dbo.ProcessFileRequest
    @Action VARCHAR(50),
    @FileType VARCHAR(50),
    @MetadataJson NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- 解析JSON为临时表
    DECLARE @Metadata TABLE (
        [Key] VARCHAR(50),
        Value VARCHAR(255) NOT NULL
    );

    INSERT INTO @Metadata ([Key], Value)
    SELECT j.[Key], j.Value
    FROM OPENJSON(@MetadataJson)
    WITH (
        [Key] VARCHAR(50) '$.Key',
        Value VARCHAR(255) '$.Value'
    ) j;

    -- 校验Key的合法性，非法则抛出错误
    IF EXISTS (
        SELECT 1 
        FROM OPENJSON(@MetadataJson)
        WITH ([Key] VARCHAR(50) '$.Key')
        WHERE [Key] NOT IN ('FileNumber', 'Id', 'Year', 'Month')
    )
    BEGIN
        THROW 50001, 'Metadata包含无效的Key值，仅允许FileNumber/Id/Year/Month', 1;
    END

    -- 后续逻辑和TVP方案一致，比如过滤数据
    SELECT * 
    FROM YourTargetTable
    WHERE 
        EXISTS (
            SELECT 1 FROM @Metadata m
            WHERE 
                CASE m.[Key]
                    WHEN 'FileNumber' THEN YourTargetTable.FileNumber
                    WHEN 'Id' THEN CAST(YourTargetTable.Id AS VARCHAR(255))
                    WHEN 'Year' THEN CAST(YourTargetTable.Year AS VARCHAR(255))
                    WHEN 'Month' THEN CAST(YourTargetTable.Month AS VARCHAR(255))
                END = m.Value
        );
END

优缺点 #

• 优势：
  • 无需提前创建表类型，部署简单，适合快速开发。
  • 应用层传递参数更方便，直接把Metadata序列化为JSON字符串即可，不用构造特殊数据结构。
• 劣势：
  • 弱类型依赖手动校验：必须自己写逻辑检查Key的合法性，容易遗漏。
  • 性能略差：大数据量时，JSON解析会产生额外开销，效率不如TVP。
  • 代码复杂度稍高：解析和校验逻辑增加了存储过程的代码量，不如TVP直观。

方案选择建议 #

作为首次处理这类需求的开发者，优先选表值参数：

• 强类型约束能帮你提前拦截非法参数，减少后续逻辑的异常处理工作。
• 表变量的操作逻辑符合常规SQL写法，更容易调试和维护，降低出错概率。

如果你的场景是快速原型开发，或者应用层对TVP的支持不佳（比如某些小众语言），可以考虑JSON方案，但一定要把Key的校验逻辑写全，避免脏数据进入存储过程。

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