title: How to create a function that returns a comma separated list of values?
body: <p>I am trying to create a function that returns a comma separated list of values based on a table name and column name parameters.</p>
  
  <p>The idea was to build a dynamic SQL statement on the fly and then return the result.  However, I ran into a problem where it won't let me run the <code>execute</code> statement inside of a function.  Here is what I had:</p>
  
  <pre><code>ALTER FUNCTION fn_HearingAttendeesToCSV (@TableName varchar(100), 
                                           @ColumnName varchar(100))
  RETURNS VARCHAR(MAX)
  AS
  BEGIN
  declare @sql varchar(max)
  
  select @sql = 
  'DECLARE @listStr VARCHAR(MAX)
      SELECT @listStr = COALESCE(@listStr + '', '' ,'''') + ' 
            + @ColumnName + ' FROM ' + @TableName + ' RETURN @listStr '
  
  Exec (@sql)
  END
  GO
  </code></pre>
  
  <p>I am pretty sure that I am going down the wrong road.  Can someone suggest how to get around the <code>execute</code> issue?  Or a better way of doing this?</p>