udfGetColumnList( )

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