如何在SQL Server存储过程中传递字符串数组作为可选参数

问题分析 #

你之前尝试用表值参数时出现语法错误，原因是：

• 表值参数的声明不能额外指定列名（dbo.SchoolSubject(Subject VARCHAR)是错误写法）
• 表值参数不能直接设置默认值= NULL，需通过判断表行数处理可选逻辑
• 原存储过程的WHERE子句存在括号不匹配的语法错误

下面提供两种可行方案，可根据你的调用习惯选择：

方案一：使用表值参数（TVP） #

适合需要传递结构化数组的场景，步骤如下：

1. 正确创建表类型 #

CREATE TYPE dbo.SchoolSubject AS TABLE(Subject VARCHAR(4) NULL); -- 匹配Subject的实际长度，示例为4位

2. 修改存储过程 #

CREATE OR ALTER PROCEDURE vle_sp_getCurrentCourses
  @Faculty AS VARCHAR(3),
  @CurrentTerm AS VARCHAR(3),
  @YearDiff AS INT = 1,
  @Subjects dbo.SchoolSubject READONLY -- 表值参数必须添加READONLY关键字
WITH EXECUTE AS 'dbo' 
AS
BEGIN
  DECLARE @SQL_QUERY NVARCHAR(MAX);
  DECLARE @PARAMS NVARCHAR(1000);
  DECLARE @NewTerm VARCHAR(4);

  SET @NewTerm = CAST(CAST(@CurrentTerm AS INT) + @YearDiff AS VARCHAR(4)); -- 强制指定长度避免截断

  -- 更新参数列表，加入表值参数
  SET @PARAMS = N'@Faculty VARCHAR(3), @CurrentTerm VARCHAR(3), @NewTerm VARCHAR(4), @Subjects dbo.SchoolSubject READONLY';
  
  SET @SQL_QUERY = N'
    SELECT mle_destination, REPLACE(mle_id, @CurrentTerm, @NewTerm) as mle_id, 
           mle_type, toenable, available, template_id, 
           start_date_rule, end_date_rule, destination_type
    FROM mle_object
    WHERE
      SUBSTRING(mle_id, 18, 3) = @CurrentTerm -- 无通配符时用=替代LIKE更高效
      AND (
        SUBSTRING(mle_id,7,4) IN (SELECT Subject FROM @Subjects) 
        OR (SELECT COUNT(*) FROM @Subjects) = 0 -- 表为空时忽略该过滤条件
      )
      AND mle_type = ''COURSE''
      AND (
          @Faculty = ''BMH'' AND SUBSTRING(mle_id, 1, 5) IN (''I3011'',''I3114'',''I3115'',''I3116'',''I2018'',''I3032'',''I3036'',''I3037'',''I3038'',''I3040'',''I3056'',''I3057'',''I3058'',''I3071'',''I3072'',''I3073'',''I3074'',''I3075'',''I3089'',''I3090'',''I3091'',''I3092'',''I3093'',''I3094'',''I3113'',''I3117'')
          OR
          @Faculty = ''HUM'' AND SUBSTRING(mle_id, 1, 5) IN (''I3000'',''I3009'',''I3016'',''I3083'',''I3088'',''I3026'',''I3028'',''I3029'',''I3031'',''I3041'',''I3078'',''I3080'',''I3081'')
          OR
          @Faculty = ''FSE'' AND SUBSTRING(mle_id, 1, 5) IN (''I3022'',''I3021'',''I3023'',''I3025'',''I3027'',''I2000'',''I3035'',''I3034'',''I3033'',''I3039'',''I3043'',''I3060'',''I3061'',''I3062'',''I3066'',''I3100'',''I3112'',''I3119'')
      )';

  EXEC sp_executesql @SQL_QUERY, @PARAMS, 
                     @Faculty = @Faculty, 
                     @CurrentTerm = @CurrentTerm, 
                     @NewTerm = @NewTerm,
                     @Subjects = @Subjects;
END

3. 调用方式 #

需先声明表变量并赋值：

DECLARE @SubjectList dbo.SchoolSubject;
INSERT INTO @SubjectList VALUES ('SALC'), ('AMBS');

EXEC vle_sp_getCurrentCourses @Faculty = 'HUM', @CurrentTerm = '121', @YearDiff = 2, @Subjects = @SubjectList;

-- 不传递Subjects时直接调用，自动忽略该过滤条件
EXEC vle_sp_getCurrentCourses @Faculty = 'HUM', @CurrentTerm = '121';

方案二：使用逗号分隔字符串参数（匹配你的示例调用） #

适合直接传递类似'SALC,AMBS'的字符串，无需自定义类型（SQL Server 2016及以上版本支持STRING_SPLIT函数）：

1. 修改存储过程 #

CREATE OR ALTER PROCEDURE vle_sp_getCurrentCourses
  @Faculty AS VARCHAR(3),
  @CurrentTerm AS VARCHAR(3),
  @YearDiff AS INT = 1,
  @Subjects VARCHAR(MAX) = NULL -- 可选参数，默认值为NULL
WITH EXECUTE AS 'dbo' 
AS
BEGIN
  DECLARE @SQL_QUERY NVARCHAR(MAX);
  DECLARE @PARAMS NVARCHAR(1000);
  DECLARE @NewTerm VARCHAR(4);

  SET @NewTerm = CAST(CAST(@CurrentTerm AS INT) + @YearDiff AS VARCHAR(4));

  SET @PARAMS = N'@Faculty VARCHAR(3), @CurrentTerm VARCHAR(3), @NewTerm VARCHAR(4), @Subjects VARCHAR(MAX)';
  
  SET @SQL_QUERY = N'
    SELECT mle_destination, REPLACE(mle_id, @CurrentTerm, @NewTerm) as mle_id, 
           mle_type, toenable, available, template_id, 
           start_date_rule, end_date_rule, destination_type
    FROM mle_object
    WHERE
      SUBSTRING(mle_id, 18, 3) = @CurrentTerm
      AND (
        SUBSTRING(mle_id,7,4) IN (SELECT value FROM STRING_SPLIT(@Subjects, '','')) 
        OR @Subjects IS NULL OR LEN(@Subjects) = 0 -- 参数为空时忽略过滤条件
      )
      AND mle_type = ''COURSE''
      AND (
          @Faculty = ''BMH'' AND SUBSTRING(mle_id, 1, 5) IN (''I3011'',''I3114'',''I3115'',''I3116'',''I2018'',''I3032'',''I3036'',''I3037'',''I3038'',''I3040'',''I3056'',''I3057'',''I3058'',''I3071'',''I3072'',''I3073'',''I3074'',''I3075'',''I3089'',''I3090'',''I3091'',''I3092'',''I3093'',''I3094'',''I3113'',''I3117'')
          OR
          @Faculty = ''HUM'' AND SUBSTRING(mle_id, 1, 5) IN (''I3000'',''I3009'',''I3016'',''I3083'',''I3088'',''I3026'',''I3028'',''I3029'',''I3031'',''I3041'',''I3078'',''I3080'',''I3081'')
          OR
          @Faculty = ''FSE'' AND SUBSTRING(mle_id, 1, 5) IN (''I3022'',''I3021'',''I3023'',''I3025'',''I3027'',''I2000'',''I3035'',''I3034'',''I3033'',''I3039'',''I3043'',''I3060'',''I3061'',''I3062'',''I3066'',''I3100'',''I3112'',''I3119'')
      )';

  EXEC sp_executesql @SQL_QUERY, @PARAMS, 
                     @Faculty = @Faculty, 
                     @CurrentTerm = @CurrentTerm, 
                     @NewTerm = @NewTerm,
                     @Subjects = @Subjects;
END

2. 调用方式 #

完全匹配你的示例：

-- 传递Subjects参数
EXEC vle_sp_getCurrentCourses @Faculty = 'HUM', @CurrentTerm = '121', @YearDiff = 2, @Subjects = 'SALC,AMBS';

-- 不传递Subjects参数
EXEC vle_sp_getCurrentCourses @Faculty = 'HUM', @CurrentTerm = '121';

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