How have strings within a string variable?

This works fine:

SELECT *
FROM MyTable
WHERE Code IN ('X', 'Y', 'Z')

But I want those values in a variable. If I do the below, it doesn't work because the X, Y, and Z aren't their own string:

DECLARE @.MyString varchar(50)
SET @.MyString = 'X, Y, Z'

SELECT *
FROM MyTable
WHERE Code IN (@.MyString)

Any way I can do what I'm trying to do?

Thanks for any help,

Ron

You couldn't use variables with IN statement. See this quote:

test_expression [ NOT ] IN ( subquery | expression [ ,...n ] )

expression[ ,... n ]

Is a list of expressions to test for a match. All expressions must be of the same type as test_expression.

Try to use dynamic SQL:

DECLARE @.MyString varchar(50)

SET @.MyString = '''X'', ''Y'', ''Z''' --You also need use quotes for each case

DECLARE @.MyQuery varchar(200)

SET @.MyQuery = '

SELECT *

FROM #MyTable

WHERE Code IN ('+@.MyString+')'

EXECUTE (@.MyQuery)

|||

Another method of breaking your strings into pieces is to use Jens Suessmeyer's SPLIT function. The advantage of this method is that it avoids potential SQL injection problems. An example of the split function can be found here:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17

Here are a couple of other examples of how the split function might be applied:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1314989&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1337001&SiteID=1

Still another way to do this would be to store your list as a blocked list of strings like in this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1418032&SiteID=1

|||

An alternative approach might be to do it like this:

declare @.aList table (n int identity)
insert into @.aList default values
insert into @.aList default values
insert into @.aList default values

declare @.myTable table
( rid int,
target varchar(2)
)
insert into @.myTable
select 1, 'A' union all
select 2, 'B' union all
select 3, 'X' union all
select 4, 'Z'

declare @.myString varchar(20)
set @.myString = 'X Y Z '

select a.*
from @.myTable a
inner join @.aList b
on target = rtrim(substring(@.myString, 2*n-1, 2))

-- rid target
-- --
-- 3 X
-- 4 Z