Issues with TSQL IN expression

Question

From what I gather about the IN expression, this should work:

DECLARE @list varchar(255)

SET @list = '''Item1'',''Item2'''

SELECT
   *
FROM
   Table
WHERE
   Item IN (@list)

And it should select those items in @list. The items exist in the table. If I execute the query separately for Item1 and Item2 (Item = Item1, then Item = Item2), those individual queries work. Just not with the IN. Can anyone enlighten me? Figure this is a simple one, just having a rough time finding useful information on this command.

EDIT:

I am currently doing this with dynamic stored procedures where I construct the query in a string and execute. For example, this procedure works:

ALTER PROCEDURE [dbo].[TestSproc]
@list varchar(4000)
AS
BEGIN

DECLARE @sql varchar(4000)

SET @sql =
'
SELECT
COUNT(*)
FROM
    Items
WHERE
    Item IN (' + @list + ') '

EXEC (@sql)

However, this procedure does not work. It returns 0 rows. If I run it manually for Item1, then Item2, it returns both counts as expected:

ALTER PROCEDURE [dbo].[TestSproc]
    @list varchar(4000)
AS
BEGIN

SELECT
    COUNT(*)
FROM
    Items
WHERE
    Item IN (@list) 

I use the same command to call both procedures:

EXEC    [dbo].[TestSproc]
    @list = N'''Item1'',''Item2'''

I tried to summarize in my original question, but I think it may have thrown people off base. Maybe this will help clear up my issue (barring any dumb typos I made).

Answer 1

The in() clause expects a discreet set of values, not a string. To make this work you will need to dynamically create the entire SQL query as a string and then execute that string.

Try something like this:

declare @list varchar(255);
set @list = '''Item1'',''Item2''';

declare @query = varchar(max);
set @query = 'SELECT
   *
FROM
   Table
WHERE
   Item IN (' + @list + ')';

exec(@query);

Answer 2

The only way to make that work is to use dynamic sql which is a poor idea. Better to take the values and put them in a temp table and join to it.

Answer 3

See here: http://www.sommarskog.se/arrays-in-sql-2005.html#CSV

Answer 4

The IN keyword doesn't operate on a list in a string. It operates on a list of values returned in a query (or on a discrete set of values such as ('Item1','Item2'), as DaveE mentions in his comment). You could modify @list like so:

DECALRE @list TABLE (
    value varchar(MAX))

INSERT INTO @list (value)
VALUES ('Item1')

INSERT INTO @list (value)
VALUES ('Item2')

SELECT * FROM Table
WHERE Item IN (SELECT value FROM @list)

The other option is to create a Table-Valued UDF that takes a comma separated list of values and returns the table. I would suggest against that though as that style of UDF is typically a poor performer.

Answer 5

The @list you use is a concatenated string which is treated as a single value which should be matched verbatim.

Use this instead:

DECLARE @list TABLE (value VARCHAR(255))

INSERT
INTO    @list
VALUES  ('Item1')

INSERT
INTO    @list
VALUES  ('Item2')

SELECT  *
FROM    Table
WHERE   Item IN
        (
        SELECT  value
        FROM    @list
        )

Answer 6

Then you have gathered wrongly.

The IN expression does not understand a list of values in form of a string. All it sees is one string. Because that is what you pass in.

You must pass in a an actual list of values, which means one distinct SQL expression per value, separated by actual commas.

SELECT *
FROM   Table
WHERE  Item IN ('Item1', 'Item2')

Alternatively you can do something in the spirit of this.

DECLARE @list TABLE (value varchar(50))
INSERT @list VALUES ('Item1')
INSERT @list VALUES ('Item2')

SELECT *
FROM   [Table] t INNER JOIN @list l ON l.value = t.item

Answer 7

NO, I think you got it all wrong

You should have a look at

• How to pass a list of values or array to SQL Server stored procedure?
• Convert a string array to tabular data in SQL Server
• How to pass array of values into SQL Server stored procedure using XML

Or even

--Split
DECLARE @textXML XML
DECLARE @data NVARCHAR(MAX), 
        @delimiter NVARCHAR(5)

SELECT  @data = 'A,B,C',
        @delimiter = ','

SELECT    @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML)
SELECT  T.split.value('.', 'nvarchar(max)') AS data
FROM    @textXML.nodes('/d') T(split)

Also haev a look at

• Passing an Array as Parameter to SQL Server Procedure
• Arrays and Lists in SQL Server

Answer 8

I use a custom table-valued function that can be reused. It comes in handy...especially with parameters to stored procedures and SSRS:

CREATE FUNCTION [dbo].[fn_Split]
(
  @ItemList NVARCHAR(4000),
  @delimiter CHAR(1)
)
  RETURNS @itemtable TABLE (Item NVARCHAR(50) )

AS

BEGIN
IF @delimiter IS NULL
BEGIN 
set @delimiter = ','
END

DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @Item NVARCHAR(4000)

SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)

INSERT INTO @itemtable(Item) VALUES(@Item)

IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)

SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END

Then you can use it like so:

DECLARE @list varchar(255)     

SET @list = '''Item1'',''Item2'''     

SELECT     *     
FROM     Table     
WHERE  Item IN (select * from dbo.fn_Split(@list,null))  //Right HERE 

Answer 9

this is the best source:

http://www.sommarskog.se/arrays-in-sql.html

create a split function, and use it like:

SELECT
    *
    FROM YourTable  y
    INNER JOIN dbo.splitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach

For this method to work, you need to do this one time table setup:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this function:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO 

You can now easily split a CSV string into a table and join on it:

select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')

OUTPUT:

ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)

Your can pass in a CSV string into a procedure and process only rows for the given IDs:

SELECT
    y.*
    FROM YourTable y
        INNER JOIN dbo.FN_ListToTable(',',@GivenCSV) s ON y.ID=s.ListValue

This will use an index on y.ID