NOT IN in function to parse CSV I'm trying to pass an array of items to be used in a NOT IN operation:

ALTER FUNCTION [dbo].[fnTEST] (@CODES VARCHAR)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
        T1.CODE AS 'CODE',
    (SELECT COUNT(*)
        FROM TABLE1 AS T2 WITH (NOLOCK)
        WHERE T2.CODE NOT IN (@CODES)
    ) AS 'COUNT'
    FROM TABLE1 T1 WITH (NOLOCK)
)


Then I call it like this:

SELECT * FROM fnTEST ('500,250,202,900,204,200,300,400,600,800')


That doesn't work. If I try to call it without the quotes, I get an error of too many parameters (which is expected).

Is there a solution for this?
In SQL Server 2008, the best way to do this is to not pass in a CSV list in the first place. Where does this comma-separated set of values come from?

First, create a table type:

CREATE TYPE dbo.myFoo AS TABLE(id INT PRIMARY KEY);
GO


Now, create a function that takes it as a TVP:

CREATE FUNCTION dbo.fnTest
(
  @codes dbo.myFoo READONLY
)
RETURNS TABLE
AS
RETURN 
(
    SELECT
        T1.CODE AS [CODE],
    (SELECT COUNT(*)
        FROM dbo.TABLE1 AS T2 WITH (NOLOCK)
        WHERE NOT EXISTS (SELECT 1 FROM @codes
          WHERE id = T2.CODE)
    ) AS [COUNT]
    FROM dbo.TABLE1 T1 WITH (NOLOCK)
);


To test it locally...

DECLARE @x dbo.myFoo;
INSERT @x(id) VALUES(500),(250),(202);...
SELECT * FROM dbo.fnTest(@x);


...but ultimately you would be passing that set of values from your C# code or wherever as a DataTable or other set-based struct.

For SQL Server 2005

You can't use TVPs. So the next best option is a split function, e.g. 

CREATE FUNCTION dbo.SplitInts
(
    @List       VARCHAR(MAX),
    @Delimiter  VARCHAR(32)
)
RETURNS TABLE
AS
    RETURN 
    (
      SELECT Item = CONVERT(INT, Item) 
        FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
          CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
        FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
          FROM sys.all_objects) AS n(Number)
        WHERE Number &lt;= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
      ) AS y
    );


So then your function can be:

CREATE FUNCTION dbo.fnTest
(
  @codes VARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN 
(
    SELECT
        T1.CODE AS [CODE],
    (SELECT COUNT(*)
        FROM dbo.TABLE1 AS T2 WITH (NOLOCK)
        WHERE NOT EXISTS (SELECT 1 FROM dbo.SplintInts(@codes, ',')
          WHERE Item = T2.CODE)
    ) AS [COUNT]
    FROM dbo.TABLE1 T1 WITH (NOLOCK)
);


If you can do this in CLR, it's probably going to perform a bit better. See the following blog series - they deal with splitting a string of strings but for the most part the observations hold true for splitting a string of ints as well.

http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings
http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up
http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql