Comma Seperated String To Table Type Variant

				
--**************************************
-- Name: Comma Seperated String To Table Type Variant
-- Description:This code takes a comma seperated string of values and turns them through the use of this function into a fully queryable no temporary objects required table. Can be used like: 
Select * From fn_StringToTable('Value1,Value2,Value3')
-- By: John Bridle
--
-- Inputs:A list of comma seperated values.
--
-- Returns:A fully queryable table.
--
-- Side Effects:None...known
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=726&lngWId=5--for details.--**************************************

CREATE FUNCTION fn_StringToTable(@String as varchar(5000))
RETURNS @RetTable Table (
	DataValue varchar(500)
	)
AS
BEGIN
DECLARE @ReturnedTable Table (
	DataValue varchar(500)
	)
DECLARE @BreakLoop Int
DECLARE @CharIndex Int
DECLARE @StringTmp Varchar(5000)
DECLARE @WrdTmp Varchar(500)
DECLARE @Count Int
	Set @StringTmp=@String
	Set @Count=0
	SET @BreakLoop=0
While @BreakLoop=0
		Begin
			Set @CharIndex=CHARINDEX(',',@StringTmp,1)
			If @CharIndex=0
				Begin
					Set @WrdTmp=Ltrim(Rtrim(@StringTmp))
					Set @BreakLoop=1
				End
			else
				Begin
					
Set @WrdTmp=ltrim(rtrim(Left(@StringTmp,@CharIndex-1)))
					
Set @StringTmp=Ltrim(rtrim(Right(@StringTmp,Len(@StringTmp)-Len(@WrdTmp)-1)))
				End
			
			Insert Into @ReturnedTable
			(DataValue)
			VALUES
			(@WrdTmp)
			Set @WrdTmp=''
			Set @Count=@Count+1
		If @BreakLoop=1
			Begin 
				BREAK
			End
		Else
			Begin
				CONTINUE
			End
		End
INSERT INTO @RetTable
SELECT DataValue From @ReturnedTable
RETURN
END