I almost feel like this should be a question on a DBA certification test.  It seems so simple, but for the life of me, I'm stuck.

I am trying to build a FUNCTION (not an SP!) that will create a comma separated list of values from a lookup on a child table.

First the easy part.  Here is code that does exactly what I want for  very specific set of related tables:

CREATE FUNCTION [dbo].[DealTypes] (
@MCOFundingID int)
RETURNS varchar(2000)
AS
BEGIN
DECLARE @List VARCHAR(2000)
SET @List = ''
SELECT @List = @List + ', ' + MCODealTypes.DealType FROM MCOFundingDealTypes INNER JOIN MCODealTypes ON MCOFundingDealTypes.MCODealTypeID = MCODealTypes.MCODealTypeID WHERE MCOFundingDealTypes.MCOFundingID = @MCOFundingID ORDER BY MCODealTypes.SortOrder
RETURN SUBSTRING(@List, 3, 2000)
END

The issue arises when I try to generalize this function to work with any parent/child table pair.  It's obvous to me that I need to use dynamic SQL, so what I am looking for is something like... (Yes, I know this does not work):

CREATE FUNCTION [dbo].[CommaSeparatedChildren] (
@ParentRecordID int,
@ParentIdentityFieldName varchar(50),
@RelationshipTableName varchar(50),
@ChildTableName varchar(50),
@ChildIdentityFieldName varchar(50),
@ChildValueFieldName varchar(50),
@ChildSortFieldName varchar(50))

RETURNS varchar(2000)
AS
BEGIN
DECLARE @List VARCHAR(2000)
SET @List = ''
SET @List = EXEC ('SELECT @List = @List + '','' + ' + @ChildTableName + '.' + @ChildValueFieldName + ' FROM ' + @RelationshipTableName + ' INNER JOIN ' + @ChildTableName + ' ON ' + @RelationshipTableName + '.' + @ChildIdentityFieldName + ' = ' + @ChildTableName + '.' + @ChildIdentityFieldName + ' WHERE ' + @RelationshipTableName + '.' + @ParentIdentityFieldName + ' = ' + @ParentIdentityFieldName + ' ORDER BY ' + @ChildTableName + '.' + @ChildSortFieldName)
RETURN SUBSTRING(@List, 3, 2000)
END

Can anyone tell me a way (preferable an elegant way ;-) ) to get this function?  Ideally, I'd like a solution that works for both SS2005 and SS2000, but will settle for something that works in SS2005.

Thanks!

--Tad Richard

I can't personally think of a way of doing this in a T-SQL function due to the restrictions imposed with regards to dynamic SQL, but have you considered writing a CLR function to perform the task?

Chris

Chris,

I had but -- unless I'm mistaken -- I can only create CLR functions for SS2005.  I'm also under the impression that CLR functions are not nearly as efficient as dynamic TSQL.

It may come to that, but I was hoping that someone much smarter than I would be able to point out some obscure feature or syntax that I was missing.

--Tad

Tad,

I was working on a very similar problem and ran into two brick walls.

User-defined functions don't allow dynamic T-SQL to be executed within the function. This prevents passing a SQL statement to the function as a parameter and executing it internally.

My work-around idea for that was to pass the completed recordset to the function as an argument, and just have the function convert it to a delimited list. Unfortunately, SQL 2000 and 2005 don't accept table datatypes as function input parameters.

However, SQL 2008 will accept a table datatype, so you can probably design a function to accept a general recordset as a parameter and flip it to a delimited list.

My interim result was similar to yours above, just have a function for each query you need to flip and pass it the key value. The performance is acceptable, and much better than having the destination application parse a cartesian product out of the recordset.

Ken,

It's funny how we think alike -- those were exactly the solutions I treid and the problems I ran into.  I have not looked at SS2008 yet, though.  Perhaps there's hope on the horizon -- especially since I can develop an SP to generate the input table parameter to the function.

Guess I'll stick with specific functions for now. Thanks for validating that I'm not completely loopy (yet.)

--Tad

Hmm... I'd do it a slightly different way. But it works! The Comma-delimited list maker is ArrayTolist. I try it out with a simple query from Northwind, but it will use any simple query that produces a ranked list of items (which is what a comma-separated list is, I suppose) . It is all explained in the TSQL String Array Workbenches on this site

CREATE FUNCTION ArrayToList
(  
@TheArray XML
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @csv VARCHAR(MAX)
SELECT   @csv=COALESCE(@csv+',','')
            +x.y.value('item[1]', 'VARCHAR(200)')
FROM     @TheArray.nodes('//stringarray/element') AS x (y)
ORDER BY x.y.value('seqno[1]', 'INT')
RETURN @CSV
END
GO
--and now we test it out on northwind
SELECT dbo.ArrayToList( (SELECT 
   [item]=categoryName, 
   [seqno]=categoryID FROM northwind.dbo.categories
                   FOR
                     XML PATH('element'),
                         TYPE,
                         ELEMENTS,
                         ROOT('stringarray')))

--Beverages,Condiments,Confections,Dairy Products,Grains...