create table my_contracts(
customer_id int,
contract_id int
)


insert into my_contracts values (1,100)
insert into my_contracts values (1,101)
insert into my_contracts values (2,102)

select * from my_contracts

create FUNCTION my_list_static
(
	-- Add the parameters for the function here
)
RETURNS varchar(1000)
AS
BEGIN

	declare @list varchar(1000)
	select @list= isnull(@list + ', ','') + convert(varchar(1000),contract_id) from contracts 
	where customer_id= 1 order by contract_id

	RETURN @list

/*
select dbo.my_list_static() as list_of_contracts
*/

END
GO

alter FUNCTION my_list_dynamic
(
	@table varchar(100),
	@field_to_list varchar(100),
	@criteria varchar(100) 
)
RETURNS varchar(1000)
AS
BEGIN

	declare @list varchar(1000)
	declare @sql varchar(1000)
	
	set @sql='select @list= isnull(@list + '', '','''') + convert(varchar(1000),' +  @field_to_list + ')'
	set @sql= @sql + ' from ' + @table 
	set @sql= @sql + ' where ' + @criteria + ' order by ' + @field_to_list
	

	-- this is what I find in @sql:
	--select @list= isnull(@list + ', ','') + convert(varchar(1000),contract_id) from my_contracts where customer_id= 1 order by contract_id
	
-- here I try to fetch the value into the variable by exec but it fails!
	exec @sql

	RETURN @list

END
GO

/*
select dbo.my_list_dynamic('my_contracts', 'contract_id','customer_id= 1') as list_of_contracts
*/

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class CLRFunctions
{
 [SqlFunction]
  public static SqlString fnCSV(SqlString columnName, SqlString tableName) 
  {
    // as usual, connection strings shouldn't be hardcoded for production code
    using(SqlConnection conn = new SqlConnection(
                                    "server=MyServer; database=AdventureWorks; " +
                                    "user id=MyUser; password=MyPassword")) 
    {
      conn.Open();

      //Build up your CSV-making query here
      SqlCommand cmd = new SqlCommand(
                     "SELECT " + columnName + " FROM " + tableName, conn);
  
      SqlDataReader r = cmd.ExecuteReader();

      string CSVResults = "";

      while(r.Read()) 
      {
                CSVResults  += r["" + columnName + ""].ToString() + ",";
      }
    }
    return CSVResults;
  }
}