USE Admin
IF
OBJECT_ID('dbo.udfGetColumnList')
Is Not Null
DROP FUNCTION dbo.udfGetColumnList
go
CREATE FUNCTION dbo.udfGetColumnList(
@ObjName sysname,
@UseBrackets bit
)
RETURNS varchar(max)
AS
/* DATE AUTHOR REMARKS
9/9/14 PPaiva Initial creation.
DESCRIPTION
Returns
a comma-separated list of columns based
on
@ObjName from vwDictCol.
DEPENDENCY
vwDictCol
USAGE
SELECT
dbo.udfGetColumnList('Employee', 1)
SELECT
* FROM vwDictCol where ObjName = 'Employee'
SELECT
dbo.udfGetColumnList(TableName, 1) ColList,
*
FROM
vwRowCount
DEBUG
SELECT
*
FROM
vwDictCol
*/
BEGIN
DECLARE @t TABLE( ColID int,
ColName sysname
)
DECLARE @MaxColID int,
@ColID int = 0,
@ColName sysname,
@Out varchar(max) = ''
INSERT INTO @t
SELECT ColID, ColName
FROM vwDictCol
WHERE ObjName = @ObjName
ORDER BY 1
SELECT @MaxColID = Max(ColID)
FROM @t
WHILE @ColID < @MaxColID
BEGIN
-- Get next ColID
SELECT @ColID = Min(ColID)
FROM @t
WHERE ColID > @ColID
-- Get ColName
SELECT @ColName = ColName
FROM @t
WHERE ColID = @ColID
IF @UseBrackets = 1
SET @ColName = '[' + @ColName + ']'
SET @Out += @ColName
IF @ColID <> @MaxColID
SET @Out += ', '
END
RETURN @Out
END
