Table Functions in SQL Server 2000 and up

I recently added a customer tagging feature to our billing system. Users needed to be able to see a list of associated tags in their reports, so I needed to be able to collect all tags for a customer into a single string. Below is a rough approximation of the table schema. I did some research, and ended up creating a user-defined function (UDF) that returns a TABLE variable. Inside the UFD, I used a table variable, a cursor (which I wanted to avoid) and ran a query with the COALESCE() function for each customer. I was able to use my UDF as if it were a temporary table in my reports. Otherwise, I would have had to duplicate this code for each report.

Before accepting this solution, I tried various flavors of selecting, updating, etc. but I couldn't find a solution without using a cursor. The code below is an untested generic adaptation of my working solution.


TABLE: Customers
Customer_ID INTEGER,
CustomerName VARCHAR(50)

TABLE: Membership
Member_ID INTEGER,
Customer_ID INTEGER,
Group_ID INTEGER

TABLE: Groups
Group_ID INTEGER
Group_Code CHAR(4)
GroupName VARCHAR(50)

-- Get All Customer Tags --
CREATE FUNCTION dbo.GetAllTags()
RETURNS @tCustTags TABLE ( Cust_ID INTEGER NOT NULL, Codes varchar(255) NULL )
AS
BEGIN

-- POPULATE TABLE WITH CUSTOMERS
INSERT INTO @tCustTags (Cust_ID)
SELECT t.Cust_ID
FROM dbo.Membership t
GROUP BY t.Cust_ID
ORDER BY t.Cust_ID ;

DECLARE @Cust_ID INTEGER, @Codes varchar(255)

DECLARE CustCode_Cursor CURSOR FOR SELECT Cust_ID, Codes FROM @tCustTags ;

OPEN CustCode_Cursor ;

WHILE 1=1
BEGIN
FETCH NEXT FROM CustCode_Cursor INTO @Cust_ID, @Codes ;
IF @@FETCH_STATUS != 0 BREAK ;
SELECT @Codes = COALESCE(@Codes+',', '')+tl.Code
FROM dbo.Membership t
JOIN dbo.Groups tl ON tl.Group_ID = t.Group_ID
WHERE t.Cust_ID = @Cust_ID ;
UPDATE @tCustTags SET Codes = @Codes
WHERE CURRENT OF CustCode_Cursor ;
END

CLOSE CustCode_Cursor ;
DEALLOCATE CustCode_Cursor ;

RETURN

END

GO