dbo.ListToTable

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[ListToTable] (
/*
FUNCTION ListToTable
Usage: select entry from listtotable('abc,def,ghi') order by entry desc
PURPOSE: Takes a comma-delimited list as a parameter and returns the values of that list into a table variable.
*/
@mylist varchar(8000)
)
RETURNS @ListTable TABLE (
seqid int not null,
entry varchar(255) not null)

AS

BEGIN
DECLARE
@this varchar(255),
@rest varchar(8000),
@pos int,
@seqid int

SET @this = ' '
SET @seqid = 1
SET @rest = isnull(@mylist,'')
SET @pos = PATINDEX('%,%', @rest)
WHILE (@pos > 0)
BEGIN
set @this=substring(@rest,1,@pos-1)
set @rest=substring(@rest,@pos+1,len(@rest)-@pos)
INSERT INTO @ListTable (seqid,entry) VALUES (@seqid,@this)
SET @pos= PATINDEX('%,%', @rest)
SET @seqid=@seqid+1
END
set @this=@rest
INSERT INTO @ListTable (seqid,entry) VALUES (@seqid,@this)
RETURN
END

Posted in: SQL,SQL UTILITY FUNCTIONS