This comes in very handy with Reporting Services (SSRS) when dealing with it's Multi-Valued parameters.

I have used a routine like this heavily for this very purpose for some time now. I have produced a function tailored for use with reporting services parameters but is relevant to any scenario where you are dealing with an array of id's passed in one parameter or field.

I can't take credit for the guts of the procedure as much of it was taken from articles like this. But I include it here because it takes this articles point further and provides a relatively elegant solution to a fairly ugly problem;

Create Function General.ufn_SplitIntegers

(@String varchar(Max))

Returns @Results Table (Value Integer Primary Key)

As

-- ------------------------------------------------------------------------------------------------

-- This function takes a string containing an array and splits the array out and returns it as a

-- table object. This is particularly useful for handling multi value parameters in Reporting

-- Services.

--

-- There are variants of this routine. This one is tuned to assume a delimiter of comma and that

-- all values passed are integers. Others allow for user defined delimiter and dealing with text values.

--

-- To use it code as follows;

--SELECT Value FROM General.ufn_SplitIntegers('3,23,45,2,6')

-- or

--SELECT Value FROM General.ufn_SplitIntegers(@Parameter)

-- ------------------------------------------------------------------------------------------------

Begin

Declare @index Int, @slice varchar(Max), @Delimiter char(1)

Select @index = 1, @Delimiter = ','

If @String Is Null Return

While @index != 0

Begin

-- Get the Index of the first occurence of the Split character

Select @index = CharIndex(@Delimiter, @String)

-- Now push everything to the left of it into the slice variable

If @index !=0

Select @slice = Left(@String, @index - 1)

Else

Select @slice = @String

-- Put the item into the results set

Insert Into @Results(Value) Values(@Slice)

-- Chop the item removed off the main string

Select @String = Right(@String, Len(@String) - @index)

-- Break out if we are done

If Len(@String) = 0 Break

End

Return

End

GO