dbo.TableToString

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER FUNCTION [dbo].[TableToString] (
/*
FUNCTION ListToTable
Usage: select dbo.TableToString('2,13') 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 varchar(1000)
as
BEGIN
DECLARE @ListTable TABLE (
seqid int not null,
entry varchar(255) not null)




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

SET @this = ' '
SET @seqid = 1
SET @rest = @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,RTRIM(LTRIM(@this)))
SET @pos= PATINDEX('%,%', @rest)
SET @seqid=@seqid+1
END
set @this=@rest
INSERT INTO @ListTable (seqid,entry) VALUES (@seqid,@this)
-- RETURN


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

declare @pages varchar(200)
SELECT @pages = COALESCE(@pages + ', ', '') + convert( varchar(50),Category)FROM

(
select Category from tbl_Category,

(select entry from listtotable(@mylist)) newtable
where tbl_Category.ID=cast (newtable.entry as bigint)

) as new

return @pages
END

Posted in: SQL,SQL UTILITY FUNCTIONS