Multi parameter CLR aggregate in SQL Server 2005


By:   |   Updated: 2010-06-21   |   Comments (3)   |   Related: More > T-SQL


Development Best Practices for SQL Server

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Attend this webinar to learn about development best practices for SQL Server. Andy Warren will share his many years of experience to give some pointers on what has worked best for him and how you can utilize some of this knowledge.


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 Updated: 2010-06-21


get scripts

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



Comments For This Article




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

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 (25108)

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 (20535)
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..


download





Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

Find MAX value from multiple columns in a SQL Server table

SQL Server Loop through Table Rows without Cursor














get free sql tips
agree to terms