Recently I had to find a solution for the following task: JQuery UI plugin makes a comma-delimited string of values, C# passes this string to SqlCommand object as a parameter, then calls a stored procedure in SQL Server. The stored procedure must filter rows in a big table by these values. One of the solution is to parse a string of values into a table, then join with a big table.
Quick search with Google helps me to find an appropriate solution in Brad Schulz blog (you can read the full article here).
I’ve found an excellent article written by Erland Sommarskog Arrays and Lists in SQL Server with probably all possible solutions.
Here is an extract of the most important part.
SELECT x.i.value('(./text())', 'varchar(2)')
SELECT XMLList = CAST('<i>' + REPLACE(@string, ',', '</i><i>') + '</i>' AS XML).query('.')
CROSS APPLY XMLList.nodes('i') x(i);
Be careful with varchar(2) in first line. It should correspond with the max length of values in your string. As you see in the screenshot below the last value 100 in the string is truncated to 10 (only 2 chars) in the last row.