如何在SQL Server函数的单个参数中传入多个字符串

你的问题在于，当你把逗号分隔的字符串传给@name参数后，WHERE name IN (@name)并不会把字符串拆分成多个值，而是把整个字符串当作单个条件去匹配，自然查不到结果。下面给你几种可行的解决方案：

SQL Server 2016开始提供了STRING_SPLIT函数，可以直接拆分逗号分隔的字符串。修改你的函数如下：

ALTER FUNCTION dbo.GetCount (@name VARCHAR(8000)) 
RETURNS INT
AS
BEGIN
    DECLARE @TheResultCount INT;  

    SELECT @TheResultCount = COUNT(*) 
    FROM Students 
    WHERE name IN (SELECT value FROM STRING_SPLIT(@name, ','));

    RETURN @TheResultCount;
END;

调用方式不变：

SELECT dbo.GetCount('abc,def,ghi,jkl'); -- 此时会返回4，符合预期

如果你的SQL Server版本低于2016，需要自己写一个拆分函数。先创建拆分函数：

CREATE FUNCTION dbo.SplitString (@InputString VARCHAR(8000), @Delimiter CHAR(1))
RETURNS @OutputTable TABLE (Value VARCHAR(1000))
AS
BEGIN
    DECLARE @StartIndex INT, @EndIndex INT

    SET @StartIndex = 1
    IF SUBSTRING(@InputString, LEN(@InputString) - 1, LEN(@InputString)) <> @Delimiter
    BEGIN
        SET @InputString = @InputString + @Delimiter
    END

    WHILE CHARINDEX(@Delimiter, @InputString) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @InputString)
        INSERT INTO @OutputTable(Value)
        SELECT SUBSTRING(@InputString, @StartIndex, @EndIndex - @StartIndex)
        SET @InputString = SUBSTRING(@InputString, @EndIndex + 1, LEN(@InputString))
    END

    RETURN
END;

然后修改你的统计函数，调用这个自定义拆分函数：

ALTER FUNCTION dbo.GetCount (@name VARCHAR(8000)) 
RETURNS INT
AS
BEGIN
    DECLARE @TheResultCount INT;  

    SELECT @TheResultCount = COUNT(*) 
    FROM Students 
    WHERE name IN (SELECT Value FROM dbo.SplitString(@name, ','));

    RETURN @TheResultCount;
END;

调用同样是SELECT dbo.GetCount('abc,def,ghi,jkl');

如果需要频繁传递多个值，表值参数是更优雅的选择，避免字符串拆分的潜在问题。

首先创建一个表类型：

CREATE TYPE dbo.NameList AS TABLE (Name VARCHAR(20));

然后修改函数接受这个表值参数：

ALTER FUNCTION dbo.GetCount (@names dbo.NameList READONLY) 
RETURNS INT
AS
BEGIN
    DECLARE @TheResultCount INT;  

    SELECT @TheResultCount = COUNT(*) 
    FROM Students s
    JOIN @names n ON s.name = n.Name;

    RETURN @TheResultCount;
END;

调用时需要先声明表变量并插入值：

DECLARE @inputNames dbo.NameList;
INSERT INTO @inputNames VALUES ('abc'), ('def'), ('ghi'), ('jkl');
SELECT dbo.GetCount(@inputNames);

这种方式更适合复杂场景，也避免了字符串长度限制和拆分错误的问题。

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