問題描述

可能的重復:
參數化 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) )

這篇關于將變量傳遞到 SQL 函數中的 IN 子句中?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！