Problems with TSQL IN Expression

Question

From what I compile in the IN statement, this should work:

DECLARE @list varchar(255)

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

SELECT
   *
FROM
   Table
WHERE
   Item IN (@list)

And he should select these items in the @list. Elements exist in the table. If I execute the query separately for Item1 and Item2 (Item = Item1, then Item = Item2), these separate queries work. Just not with IN. Can anyone enlighten me? The drawing is simple, just with a rough time finding useful information about this command.

EDIT:

I am currently doing this with dynamic stored procedures where I am building a query in a row and executing. 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 started it manually for Item1, then Item2, it will return both values ​​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'''

, , , , . , ( , ).

Answer 1

IN . , ( , ('Item1','Item2'), DaveE ). @list :

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)

UDF, , , . , , UDF .

Answer 2

- sql, . temp .

Answer 3

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

Answer 4

in() , . SQL- , .

- :

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

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

exec(@query);

Answer 5

@list - , , .

:

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

.

IN . , , . , .

, SQL , .

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

- .

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

, ,

• SQL Server ?
• SQL Server
• SQL XML

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

• SQL
• SQL Server

Answer 8

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

:

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

:

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

:

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

, :

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)

Numbers , :

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 

CSV :

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

:

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

(6 row(s) affected)

CSV :

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

y.ID