Multi parameter CLR aggregate in SQL Server 2005

By: Andy Novick   |   Read Comments (3)   |   Related Tips: More > Aggregates

Problem

The very useful concat aggregate in the article How to pass multiple arguments to a CLR aggregate in SQL Server 2008 accepts multiple arguments and returns concatenated strings delimited by any string requested.  That's more useful than the Concatenate aggregate from the article SQL Server CLR function to concatenate values in a column, which was orignally part of the Microsoft Sample code.  There are still many systems running SQL Server 2005 and they could benefit from aggregates that accept multiple parameters.  How is it possible to pass multiple parameters to a SQL Server 2005 CLR aggregate?

Solution

SQL Server 2005 introduced user-defined aggregate functions but restricted them to have one input parameter and to have a result that is 8000 bytes or less.   SQL Server 2008 lifts those restrictions and allows multiple parameters and results that are up to two gigabytes in size.  The restriction on the number of parameters can be sidesteped in SQL Server 2005 by turning multiple parameters into one parameter.

I had oringially intended to write up this tip and show how to pass a CLR User-Defined Type (UDT) to the aggregate as a way to pass the multiple arguments.  UDT's are data types based on CLR types and they're described in the article SQL Server User-Defined Type To Store and Process Email Addresses. As I began to write the Parse method of the UDT, which is mandatory, I decided that the two paramaters would be seperated by a zero byte (0X00) or CHAR(0).  The Parse method had to find the zero byte, split up the input string and use the results to populate the properties of the UDT.  But if Parse method could do this, so could the Accumulate method of the aggregate.  There wasn't a need for the UDT and the solution is:

• Combine arguments by converting them to string and passing the result to the aggregate
• Concatenate them with Char(0) as the seperator between parameters
• Split the arguments in the Aggregate method of the aggregate

This approach eliminates the need to create the UDT and is thus much simpler.  While the time to perform the string concatenation and parsing might be a little longer than the time to construct a CLR type and populate it, the difference can't be much and due to the size limitation on the aggregate of 8000 bytes it's unlikely to be noticed.

Using Visual Studio 2005 and SQL Server 2005 a C# SQL Project contains the aggregate.  The process of creating a SQL CLR project with Visual Studio has been described in earlier articles such as Writing to an operating system file using the SQL Server SQLCLR.  The C# SQL Server Project is in the Project Type "Visual C#\Database" in the New Project dialog.  Start by adding an aggregate. I named mine concat5 using the menu command "Project\Add Aggregate...".   The code is very similar to the code found in How to pass multiple arguments to a CLR aggregate in SQL Server 2008 the differences is that the Aggregate method has the additional job of splitting the string into it's parts.  Here's the code:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text;

[Serializable]

[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(

    Format.UserDefined,

    MaxByteSize = 8000

    )]

public struct concat5 : IBinarySerialize

{

    private StringBuilder _accumulator;

    private string _delimiter;

    private Boolean _IsNull;

    public Boolean IsNull

    {

        get { return _IsNull; }

    }

    public void Init()

    {

        _accumulator = new StringBuilder();

        _delimiter = ",";

        this._IsNull = true;

    }

    public void Accumulate(SqlString Value)

    {

        string newvalue;

        string newdelimiter;

        int zero_pos = Value.Value.IndexOf((char)0x00);

        if (zero_pos >= 0)

        {

            newvalue = Value.Value.Substring(0, zero_pos);

            newdelimiter = Value.Value.Substring(zero_pos + 1

                , Value.Value.Length - zero_pos - 1);

            _delimiter = newdelimiter;

        }

        else

        {

            newvalue = Value.Value;

        }

        if (_delimiter.Length > 0

           & _accumulator.Length > 0) _accumulator.Append(_delimiter);

        _accumulator.Append(newvalue);

        if (Value.IsNull == false) _IsNull = false;

    }

    /// <summary>

    /// Merge onto the end 

    /// </summary>

    /// <param name="Group"></param>

    public void Merge(concat5 Group)

    {

        /// add the delimiter between strings

        if (_accumulator.Length > 0

            & Group._accumulator.Length > 0) _accumulator.Append(_delimiter);  

         _accumulator.Append(Group._accumulator.ToString());

    }

    public SqlString Terminate()

    {

        // Put your code here

        return new SqlString(_accumulator.ToString());

    }

    /// <summary>

    /// custom deserialize from the reader

    /// </summary>

    /// <param name="r">BinaryReader.</param>

    void IBinarySerialize.Read(System.IO.BinaryReader r)

    {

        _delimiter = r.ReadString();

        _accumulator = new StringBuilder(r.ReadString());

        if (_accumulator.Length != 0) _IsNull = false;

    }

    /// <summary>

    /// Custom erialization method.

    /// </summary>

    /// <param name="w">BinaryWriter.</param>

    void IBinarySerialize.Write(System.IO.BinaryWriter w)

    {

        w.Write(_delimiter);

        w.Write(_accumulator.ToString());

    }

}

Use concat5 the same way the SQL Server 2008 version is used to aggregate a column. This example uses the AdventureWorks database and produces a short list of  provinces delimited by pipe characters:

SELECT TerritoryName 
     , dbo.concat5(StateProvinceName + CHAR(0) + '|') 
   FROM AdventureWorks.Sales.vSalesPerson 
    WHERE TerritoryName IS NOT NULL 
    GROUP BY TerritoryName 
GO 
      

TerritoryName        StateProvinceList
-------------------- ----------------------------------
Australia            Victoria
Canada               Alberta|Ontario
Central              Minnesota
France               Gironde
Germany              Hamburg
Northeast            Michigan
Northwest            Oregon|Washington|Massachusetts
Southeast            Tennessee
Southwest            Utah|California
United Kingdom       England

To make using concat5 a bit easier comma is used as a default seperator.  That allows the Char(0) and deliminator to be omitted and concat5 works like the Concatenate aggregate from the Microsoft samples.  It's still possible to include the Char(0) but no deliminator to have the strings joined without a deliminator.

With the original SQL Server 2005 version of this function, which was called Concatenate, we had to worry about creating aggregates that went over 8000 bytes.  That's is not a problem with the concat aggregate created for SQL Server 2008, but working in SQL Server 2005 it becomes a problem again.  Concatenating more than 8000 bytes causes SQL Server to throw an error as the size of the aggregate grows as seen here:

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "concat5": 
System.Data.SqlTypes.SqlTypeException: The buffer is insufficient. Read or write operation failed.
System.Data.SqlTypes.SqlTypeException: 
   at System.Data.SqlTypes.SqlBytes.Write(Int64 offset, Byte[] buffer, Int32 offsetInBuffer, Int32 count)
   at System.Data.SqlTypes.StreamOnSqlBytes.Write(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.BinaryWriter.Write(String value)
   at concat5.Microsoft.SqlServer.Server.IBinarySerialize.Write(BinaryWriter w)

Next Steps

• Build the concat5 aggregate for your SQL Server 2005 databases
• Create aggregates with multiple arguments in SQL Server 2005 by combining them into a single string argument seperated with zero bytes.

About the author

Andy is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

View all my tips

We Recommend

• More Database Developer Tips...

Print

Tweet

Become a paid author

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code