using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;
/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}
/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
return;
}
this.intermediateResult.Append(value.Value).Append(','); /// I want to change to comma to a variable
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
}
/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}
Hi Rob,
Unfortunately, a current limitation of our UDAs is that they can only take one parameter: the single parameter being aggregated. This precludes a few scenarios, including an additional parameter that is constant for the life of the aggregate (your situation) as well as a second aggregated parameter.
The usual workaround we recommend for folks who want to aggregate over two variables is to wrap them using either XML or a UDT and then unwrap them inside of the aggregate. This could work in your situation as welljust hold one of the variables constantbut is a particularly ugly hack.
Cheers,
-Isaac
|||Hi Isaac,Since we're considering ugly hacks, I had an idea for another workaround: could I create a table variable, and access it from within my UDA?
E.g., in the following procedure, I create a temp table with params, which my UDA will expect to find in scope when it runs. I have no idea if this would work at all...
CREATE PROCEDURE foo
AS
DECLARE @.settings table (
param1 int,
param2 char
)
SELECT lastName, dbo.myConcatUDA(firstName)
FROM people
GROUP BY lastName
GO
