/*
        This code creates a User Defined Function that parses a comma 
separated list and creates a table with a unique key.  Can be reused from 
within Stored Procedures.
*/

CREATE FUNCTION fafn_parse_delimited_list
(
        @delimitedList VarChar(8000)
)
RETURNS @tblSample TABLE (CounterID Int, FieldValue Varchar(100))

BEGIN
       	Declare        @CounterID           VarChar(4)
       	Declare        @FieldValue          Varchar(100)

       	Declare @tmpTable Table (CounterID Int Primary Key, FieldValue 
VarChar(100))

        Set @CounterID = 1

        While CharIndex(',', @delimitedList) > 0
                Begin
                           Set @FieldValue = 
LTrim(RTrim(subString(@delimitedList, 1, charIndex(',', @delimitedList)-1)))
                   Insert Into @tmpTable Select @CounterID, @FieldValue

                           Set @delimitedList = 
LTrim(RTrim(subString(@delimitedList, (charIndex(',', @delimitedList) + 1), 
Len(@delimitedList))))

                           Set @CounterID = @CounterID + 1
                End
	If  LTrim(RTrim(@delimitedList)) != ''
	   Insert Into @tmpTable Select @CounterID, @delimitedList

   Insert Into @tblSample Select * From @tmpTable

   Return
END

/* Usage */
        Select *
          From dbo.fafn_parse_delimited_list('Apples,Oranges')
/*
        Can be called from Within Stored Procedures.
*/