SQL Server 2005 Single Quoted list of strings fail

I have an SP that has an nvarchar(max) parameter @p1. This is a comma separated list of codes e.g. 100,200,300 etc

Inside the SP I declare a variable @p2 nvarchar(max) and convert the @p1 list into a single quoted list

set @p2 = N'''' + replace(@p1,',',''',''') + ''''

When I "select" @p2 this returns the correct '100', '200', '300'

If I then use @p2 in a subselect, e.g

select x,y,z from table1 where id in (@p2) 

I get no rows returned? If I modify the same SQL to use the same string literal I got from the previous select I get the rows ok?

How can I pass a string containing a list of si开发者_Go百科ngle quoted identifiers to a SP and use this in a subselect? I've Googled this extensively, and it is possible to convert the list to a temporary table inside the SP and use this, but I need to use the quoted list so I can use an OPTIMIZE FOR query hint.

IN does not work with comma-separated strings the way you expect: it is considered a single string, not an implicit array of values.

id IN ('101,102,103') will only match if id is exact string '101,102,103', not any of its individual separated chunks.

You should implement a TVF which splits the string and returns a set of its members and use it like that:

SELECT  *
FROM    table1
WHERE   id IN
        (
        SELECT  value
        FROM    tvf_split_string('101,102,103')
        )

As Quassnoi says, you should use a function to split the string. Here's one that I use:

CREATE FUNCTION dbo.tvf_split_string
(
    @List nvarchar(2000),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Value nvarchar(100)
) 
AS  
BEGIN 
    while (Charindex(@SplitOn,@List)>0)
    begin
        insert into @RtnValue (value)
        select 
            Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

        set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
    end
    insert Into @RtnValue (Value)
    select Value = ltrim(rtrim(@List))

    return
END

And call it as follows:

SELECT value FROM dbo.tvf_split_string('101,102,103')

One option is to use execute

Execute('select x,y,z from #1 where id in (' + @p2 + ')')