Passing a variable into an IN clause within a SQL function?

Question

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

Nope. There is no way to pass in a string of comma-separated values into the IN clause other than dynamic SQL. You could use a split table valued function to transform the CSV string into a table of values that you could then join on though. But as you are on 2008 passing them in as a TVP in the first place would be better... — Martin Smith, Jun 28 '12 at 11:03

score 3 · Answer 1 · answered Jun 28 '12 at 13:58

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, ','));

I should point out that, in my defence, the function I pasted was the best we could do 8 years ago. It really is that old and I have no doubt there are better ways (such as this). By the way, I'll test this for performance and replace ours ;-) — Codesleuth, Jun 29 '12 at 13:09
@Codesleuth there is no defense necessary, this wasn't meant as a "your function is bad" comment. In all honesty, they are quite comparable, and I wouldn't change an existing approach for this minuscule gain except in a very high-volume system (in which case I'd go CLR anyway). I just didn't feel the answer was complete with only the old-fashioned method presented, and felt it worthwhile to show the OP (and future readers) an alternative. — Aaron Bertrand, Jun 29 '12 at 13:11
"in which case I'd go CLR anyway" great timing - I'm writing CLR code as we speak for the same database. That function is in **heavy** use, and we could benefit from the small gain there. Also, why is the @ symbol failing today?! — Codesleuth, Jun 29 '12 at 13:13
There are string-splitting cases where CLR doesn't win compared to other approaches, but overall it's still the best choice IMHO. — Aaron Bertrand, Jun 29 '12 at 13:16
You don't need to `@notify` me if you're commenting on my answer. I get notified by default. — Aaron Bertrand, Jun 29 '12 at 13:16

score 2 · Accepted Answer · answered Jun 28 '12 at 11:12

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

Passing a variable into an IN clause within a SQL function?

2 Answers2

Linked