Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Multi parameter CLR aggregate in SQL Server 2005


By:   |   Read Comments (3)   |   Related Tips: More > T-SQL

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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:

SELECT DATALENGTH(dbo.concat5(Name CHAR(0) + ';')) [Concat Len]     
FROM 
AdventureWorks.Sales.Store
GO
     
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.


Last Update:


signup button

next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, May 23, 2013 - 12:20:40 PM - Federico Caselli Back To Top

oops, there was a null value.

Now I get this error:

 

System.Data.SqlServer.TruncationException: Trying to convert return value or output parameter of size 8052 bytes to a T-SQL type with a smaller size limit of 8000 bytes.

System.Data.SqlServer.TruncationException: 

   at System.Data.SqlServer.Internal.CXVariantBase.StringToWSTR(String pstrValue, Int64 cbMaxLength, Int32 iOffset, EPadding ePad)

   at System.Data.SqlServer.Internal.CXVariantBase.SqlStringToWSTR(SqlString strValue, Int64 cbMaxLength, Int32 iOffset, EPadding ePad)


Thursday, May 23, 2013 - 12:17:44 PM - Federico Caselli Back To Top

didn't work in my case :(

 

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

System.Data.SqlTypes.SqlNullValueException: 

   at System.Data.SqlTypes.SqlString.get_Value()

   at SQLAggregazioni.concat5.Accumulate(SqlString Value)


Monday, November 26, 2012 - 9:07:12 AM - Wim Back To Top
If you use a User defined datatype you can use the datatype to hold 2 values. If you create also a function to create the datatype based on the 2 values you don't need any string manipulation.. it will be much faster. Also.. you could use a other datatype if you want your aggregate to return multiple values, I use this for certain 'special' aggregates and the possibilities are endless..

Learn more about SQL Server tools