(c) 2003 Visual Studio Magazine 
Fawcette Technical Publications

T-SQL	Generate Comma-Delimited Lists With Scalar UDFs
Listing 1	This code shows how you can generate comma-delimited lists with scalar UDFs. The code uses a lesser-known feature of Transact-SQL syntax that you can use to iterate through the resultset, concatenate one or more values from each row, and assign the result to a variable in a single SELECT statement.

USE pubs
GO

CREATE FUNCTION dbo.GetAuthorList(@TitleID varchar(6))
RETURNS varchar(8000)
AS
BEGIN
	DECLARE @Authors varchar(8000)

	SELECT @Authors = ISNULL(@Authors + ', ', '') + 
		authors.au_fname + ' ' + authors.au_lname
	FROM titleauthor INNER JOIN
		authors ON titleauthor.au_id = authors.au_id
	WHERE titleauthor.title_id = @TitleID

	RETURN @Authors
END

T-SQL Create Your Own System UDFs
Listing 2	This code shows you how to implement a system UDF. You turn a regular function into a system function by assigning the ownership to the system_function_schema user. You don't need to enable system table modifications when you create a system UDF, but you must do it when you want to drop a system function. 

USE master
GO

-- enable system table updates
EXEC sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'fn' AND 
	name='fn_RowCountEstimate' and uid = 4)
BEGIN
	DROP FUNCTION system_function_schema.fn_RowCountEstimate
END
go

-- disable system table updates
EXEC sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO

CREATE FUNCTION dbo.fn_RowCountEstimate
(
	@TableName nvarchar(517)
)
RETURNS bigint
AS
BEGIN
	DECLARE @ID int 
	DECLARE @RowCount bigint

	SELECT @ID = id FROM dbo.sysobjects 
	WHERE id = OBJECT_ID(@TableName) AND
	(OBJECTPROPERTY(id, N'IsTable') = 1)

	IF (@ID IS NULL)
	BEGIN
		RETURN -1
	END

	SELECT @RowCount = rowcnt
	FROM dbo.sysindexes
	WHERE indid < 2 and id = @ID

	RETURN @RowCount
END
GO

EXEC sp_ChangeObjectOwner 'dbo.fn_RowCountEstimate', 'system_function_schema'
GO


