将变量传递到 SQL 函数中的 IN 子句中?

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

可能的重复:
参数化 SQL IN 子句?

我有一个 SQL 函数，我需要将一个 ID 列表作为字符串传入:

I have a SQL function whereby I need to pass a list of IDs in, as a string, into:

ID 在哪里 (@MyList)

WHERE ID IN (@MyList)

我环顾四周，大多数答案都是在 C# 中构建 SQL 并循环调用 AddParameter，或者动态构建 SQL.

I have looked around and most of the answers are either where the SQL is built within C# and they loop through and call AddParameter, or the SQL is built dynamically.

我的 SQL 函数相当大，因此动态构建查询会相当乏味.

My SQL function is fairly large and so building the query dynamically would be rather tedious.

真的没有办法将一串逗号分隔的值传入 IN 子句吗?

Is there really no way to pass in a string of comma-separated values into the IN clause?

我传入的变量表示一个整数列表，所以它是:

My variable being passed in is representing a list of integers so it would be:

1,2,3,4,5,6,7"等

"1,2,3,4,5,6,7" etc

推荐答案

将字符串直接传递到 IN 子句是不可能的.但是，如果您将列表作为字符串提供给存储过程，例如，您可以使用以下脏方法.

Passing a string directly into the IN clause is not possible. However, if you are providing the list as a string to a stored procedure, for example, you can use the following dirty method.

首先创建这个函数:

CREATE FUNCTION [dbo].[fnNTextToIntTable] (@Data NTEXT)
RETURNS 
    @IntTable TABLE ([Value] INT NULL)
AS
BEGIN
    DECLARE @Ptr int, @Length int, @v nchar, @vv nvarchar(10)

    SELECT @Length = (DATALENGTH(@Data) / 2) + 1, @Ptr = 1

    WHILE (@Ptr < @Length)
    BEGIN
        SET @v = SUBSTRING(@Data, @Ptr, 1)

        IF @v = ','
        BEGIN
            INSERT INTO @IntTable (Value) VALUES (CAST(@vv AS int))
            SET @vv = NULL
        END
        ELSE
        BEGIN
            SET @vv = ISNULL(@vv, '') + @v
        END

        SET @Ptr = @Ptr + 1
    END

    -- If the last number was not followed by a comma, add it to the result set
    IF @vv IS NOT NULL
        INSERT INTO @IntTable (Value) VALUES (CAST(@vv AS int))

    RETURN
END

(注意:这不是我的原始代码，但由于我工作场所的版本控制系统，我丢失了链接到源代码的标题注释.)

(Note: this is not my original code, but thanks to versioning systems here at my place of work, I have lost the header comment linking to the source.)

然后像这样使用它:

SELECT  *
FROM    tblMyTable
        INNER JOIN fnNTextToIntTable(@MyList) AS List ON tblMyTable.ID = List.Value

或者，如您的问题:

SELECT  *
FROM    tblMyTable
WHERE   ID IN ( SELECT Value FROM fnNTextToIntTable(@MyList) )

相关文章