Passing a variable into an IN clause within a SQL

问题:

Possible Duplicate:
Parameterizing an SQL IN clause?

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

WHERE ID IN (@MyList)

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.

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

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" etc

回答1:

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.

First, create this function:

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.)

Then use it like so:

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

Or, as in your question:

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

回答2:

Here is a slightly more efficient way to split a list of integers. First, create a numbers table, if you don't already have one. This will create a table with 100,000 unique integers (you may need more or less):

;WITH x AS
(
   SELECT TOP (1000000) Number = ROW_NUMBER() OVER 
   (ORDER BY s1.[object_id])
   FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
   ORDER BY s1.[object_id]
)
SELECT Number INTO dbo.Numbers FROM x;

CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers(Number);

Then a function:

CREATE FUNCTION [dbo].[SplitInts_Numbers]
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN
   (
       SELECT Item = CONVERT(INT, SUBSTRING(@List, Number,
         CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number))
       FROM dbo.Numbers
       WHERE Number <= CONVERT(INT, LEN(@List))
         AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
   );

You can compare the performance to an iterative approach here:

http://sqlfiddle.com/#!3/960d2/1

To avoid the numbers table, you can also try an XML-based version of the function - it is more compact but less efficient:

CREATE FUNCTION [dbo].[SplitInts_XML]
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( 
     SELECT Item = x.i.value('(./text())[1]', 'int') FROM ( 
       SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') 
       + '</i>').query('.') ) AS a CROSS APPLY [XML].nodes('i') AS x(i)) AS y
     WHERE Item IS NOT NULL
   );

Anyway once you have a function you can simply say:

WHERE ID IN (SELECT Item FROM dbo.SplitInts_Numbers(@MyList, ','));