Dynamic IN statement

I'm looking to do something like the following:

SELECT *
FROM states
WHERE abbr IN ('CA','OR','WA')

However I want to retrieve the info by using a variable like so:

DECLARE @.MyList varchar(100)
SET @.MyList = 'CA,OR,WA'

SELECT *
FROM states
WHERE abbr IN (@.MyList)

This of course does not work! I am wondering how I might achieve my goal of defining my IN statement using a variable.

Any help is appreciated.
CSDThe set @.mylist = 'CA,OR,WA' will not work. You have to store the text values individually. Try the following:

declare @.a varchar(100)
set @.a = '''CA''' + ',' + '''OR'''

exec ('select * from states where abbr in (' + @.a + ')')|||Thanks for the suggestion. I was hoping to avoid using an EXEC statement, but that looks like the only way to do it. If anyone has any thoughts on how to solve the problem without an EXEC let me know.

Thanks
CSD|||You can also do something like:

declare @.MyList nvarchar(100)
declare @.SQLStmt nchar(400)

/* As you can see the trick is in getting the single quote character in the picture which you can do by adding char(39) as starting and closing of the state abbreviation.
*/

set @.MyList = char(39) + 'CA' + char(39) + ',' + char(39) + 'OR' + char(39) + ',' + char(39) + 'WA' + char(39)

set @.SQLStmt = 'SELECT * FROM states WHERE abbr in (' + rtrim(@.MyList) + ')

exec sp_executesql @.SQLStmt|||Acctualy the single quote part is easy...

Code:
---------------------------------------
declare @.MyList nvarchar(100)
declare @.SQLStmt nchar(400)

/*
** As you can see the trick is in getting the single quote character in the picture which you can do by adding char(39) as starting and closing of the state abbreviation.
*/

set @.MyList = '''CA'',''OR'',''WA'''
set @.SQLStmt = 'SELECT * FROM states WHERE abbr in (' + @.MyList + ')'

select @.MyList
select @.SQLStmt
---------------------------------------
csdpdx; You have two options here, dynamic SQL as has been pointed out or build a temp table, stuff the diffrent state abbreviations into the table and join on the table for the answer. IMHO I would go for the dynamic sql.