SQLTeam.com | Weblogs | Forums

Need a .net like function in user defined function of sql server

sql2012

#1

I have a .net code which i need to replicate as it is in sql server. Need a same user defined function as follows. As I am not that much expert in sql server could you please help me.

My .net function is as follows.

public static string GetCsv(params string[] list)
{
string comma = "";
string ret = "";
for (int i = 0; i < list.Length; i++)
{
if (!string.IsNullOrEmpty(list[i]))
{
ret += comma + list[i];
comma = ",";
}
}
return ret;
}


#2

I have two answers:

  1. Pass a list of elements, as an array, to a function and concatenate them with "," delimiter
  2. Prepare a comma-deleted list of the values in a Column (i.e. from a Table in the database)

#2 is easy to do - if that is what you want?

#1 is not really something that you want to do in SQL. More normally you already have the Column Values in a Table in the database, and you want to concatenate them.

However, there is another scenario for #1:

1a. pass a delimited-list to a query and select all rows, in a table, that match any of those values.

For example, you want all Customers that have ID in the list (1234, 5678, 9012, ...) - in that example you can pass a delimited list, or an array, to SQL and then use those values in a SQL statement.

Personally I find passing an array a nuisance to program, so we use a delimited-list in that situation.