Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
@CODES VARCHAR will declare a parameter of length 1 but even if you fix that this won't work. What version of SQL Server are you on? –  Martin Smith Jul 17 '13 at 13:08
    
Hi Martin, version is 2005 on the production machine and 2008 on my dev machine. –  Diego Vieira Jul 17 '13 at 13:21
    
Why are you developing on 2008? You can introduce all kinds of problems if you code something that is only supported in the newer version and then try to deploy it to an older version... –  Aaron Bertrand Jul 17 '13 at 13:44

2 Answers 2

You can convert the delemited comma string into a table using Table valued function (see below function fncStrListToStringTable). You can then use the "not exists" mssql clause to eliminate and extract the data as required similar to "NOT IN" This works in SQL2000 , SQL2005 , SQL2008R2

    GO
    SET ANSI_NULLS ON

    SET QUOTED_IDENTIFIER ON
    GO
    Alter FUNCTION [dbo].[fnTEST] (@CODES VARCHAR)
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT
            T1.CODE AS 'CODE',
        (SELECT COUNT(*)
            FROM TABLE1 AS T2 WITH (NOLOCK)
            WHERE  not exists (select  rec_id from dbo.fncStrListToStringTable(@CODES) a  where a.rec_id= t2.code)
) AS 'COUNT'
FROM TABLE1 T1 WITH (NOLOCK)
    )

            GO

-- Here is the custom function code to convert delimited text to table .


/* Object: UserDefinedFunction [dbo].[fncStrListToStringTable] Script Date: 07/17/2013 18:52:36 */

    GO
    SET QUOTED_IDENTIFIER ON
    GO
    /***************************************
    Objective   :   Converts the given  string List into  table  
                Returns a table with  Company ID ,Rec_ID, and Key_ID. 
         Key_ID is unique     and is the order of the  data in ths string list
            Refer http://www.sommarskog.se/arrays-in-sql.html#tblnum-unlimited
           ***********************************************/
    Create FUNCTION [dbo].[fncStrListToStringTable] (@List text)
  RETURNS @tbl TABLE (Rec_ID varchar (500)  NOT NULL, Key_ID int identity(1,1)) 
    AS
    BEGIN
  DECLARE @pos      int,
          @textpos  int,
          @chunklen smallint,
          @str      varchar(8000),
          @tmpstr   varchar(8000),
          @leftover varchar(8000)

If @List Is Not Null
Begin
    SET @textpos = 1
        SET @leftover = ''
        WHILE @textpos <= datalength(@list) 
        BEGIN
            SET @chunklen = 8000 - datalength(@leftover) 
            SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
            SET @textpos = @textpos + @chunklen

            SET @pos = charindex(',', @tmpstr)
            WHILE @pos > 0
            BEGIN
                    SET @str = substring(@tmpstr, 1, @pos - 1)
                    INSERT @tbl VALUES(@str)
                    SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
                    SET @pos = charindex(',', @tmpstr)
            END

            SET @leftover = @tmpstr
        END

        IF ltrim(rtrim(@leftover)) <> ''
            INSERT @tbl VALUES(@leftover)
End

Else 
    INSERT @tbl VALUES(0)

RETURN
    END
share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.