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