By: Andy Novick | Updated: 2010-06-21 | Comments (3) | Related: > Functions System
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 herereturn 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:
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.
About the author
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-06-21
