Most modern web languages these days offer some function to parse a "list" of text items, which is usually a text string with multiple values separated by a delimiter, often a comma. SQL Server unfortunately does not have such an affordance, which becomes troublesome when you're passing a dynamic number of parameters into a SQL statement.
Here comes a user-defined function, which takes a string and a delimiter, and returns a table with the text items broken out into separate rows. To use it:
select t.theString from dbo.fn_ParseStringList('1,2,3', ',') t order by t.theString
And here's the code:
create function [dbo].[fn_ParseStringList] ( @input varchar(8000), @delim char(1) ) returns @output table ( theString varchar(200) ) as begin -- append the delimiter to the end of the string set @input = @input + @delim -- what is the position where the delimiter is located declare @pos int set @pos = charIndex(@delim, @input, 1) while (@pos > 0) begin if (@pos > 1) insert into @output (theString) select left(@input, @pos-1) set @input = right(@input, len(@input)-@pos) set @pos = charIndex(@delim, @input, 1) end return end GO