In this example, I create a SQL user defined aggregate function that returns a Comma Separated Value (CSV) string from a table column.

In SQL, user defined aggregate functions use CLR, so you cannot create them with SQL scripts alone, you must use C# in Visual Studio.

  1. Create a new project in Visual Studio. I used the name “MyNamespace”.
  2. Create a new class within the project. I used the name “CSV.cs”. Here is the code for the class:
    • using System.Data.SqlTypes;
      using Microsoft.SqlServer.Server;
      using System.Collections.ObjectModel;
      namespace MyNamespace{
      [System.Serializable]
      [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
      Microsoft.SqlServer.Server.Format.UserDefined,
      IsInvariantToDuplicates = false,
      IsInvariantToNulls = false,
      IsInvariantToOrder = true,
      IsNullIfEmpty = true,
      MaxByteSize = -1,
      Name = “CSV”
      )]
      public struct CSV : IBinarySerialize
      {
      private Collection<string> list;
      private string delimiter;
      public void Init()
      {
      list = new Collection<string>();
      delimiter = string.Empty;
      }
      //this seems to handle the SQL function parameters
      public void Accumulate(string value, string delimiter)
      {
      if (value != null)
      {
      if (this.delimiter != delimiter) { this.delimiter = delimiter; }
      list.Add(value.ToString().Replace(this.delimiter, “”)); //remove delimeter character from value
      }
      }
      //not sure when this gets used
      public void Merge(CSV group)
      {
      if (null != group.list && group.list.Count > 0)
      {
      foreach (string entry in group.list)
      {
      list.Add(entry);
      }
      }
      }
      //this seems to return the result
      public SqlString Terminate()
      {
      return new SqlString(string.Join<string>(“,”, list));
      }
      #region IBinarySerialize Members
      //not sure when this gets used, but it matches the function below
      public void Write(System.IO.BinaryWriter writer)
      {
      writer.Write(this.delimiter);
      foreach (string entry in list)
      {
      writer.Write(entry);
      }
      }
      //not sure when this gets used, but it matches the function above
      public void Read(System.IO.BinaryReader reader)
      {
      this.delimiter = reader.ReadString();
      list = new Collection<string>();
      while(reader.BaseStream.Position != reader.BaseStream.Length) //not end of file
      {
      this.list.Add(reader.ReadString());
      }
      }
      #endregion
      }
      }
  3. Build -> Build Solution
  4. Next we will deploy the assembly (.dll file) generated in the previous step. It should be somewhere within the “bin” folder of you solution. Open the SQL command prompt (Start -> All Programs -> Microsoft SQL Server 2012 -> Visual Studio Tools -> Developer Command Prompt for VS2012) and enter the following command in order to deploy the assembly to Global Assembly Cache (GAC). (be sure to replace the .dll path with your own file path):
    • gacutil.exe /if c:\projects\Web\MyNamespace\MyNamespace\bin\release\MyNamespace.dll
  5. Open SQL Management Studio. Register the assembly in SQL by running the following script:
    • EXEC sp_configure ‘clr enabled’, 1
      GO
      RECONFIGURE
      GOCREATE ASSEMBLY MyNamespace
      AUTHORIZATION dbo
      FROM ‘C:\projects\Web\MyNamespace\MyNamespace\bin\release\MyNamespace.dll’
      WITH PERMISSION_SET = SAFE
      GO
  6. Create the user defined aggregate function:
    • CREATE AGGREGATE dbo.CSV (
      @value nvarchar(MAX),
      @delimiter nvarchar(50)
      )
      RETURNS nvarchar(MAX)
      EXTERNAL NAME MyNamespace.[MyNamespace.CSV]
      –EXTERNAL NAME SQLAssemblyName.[C#NameSpace”.C#ClassName].C#MethodName
  7. Your user defined aggregate function should be ready for use now: run the following script: (the function works correctly with GROUP BY, but does provide any ORDER BY capability within the CSV)
    • SELECT dbo.CSV(MyColumn, ‘,’)
      FROM MyTable
  8. If you need to remove the SQL assembly reference and the user defined aggregate function, run the following script:
    • DROP AGGREGATE dbo.CSV
      DROP ASSEMBLY MyNamespace
Advertisements