×Ô¶¨Òå¶à²ÎÊýSql¾ÛºÏº¯Êý

Creating a CLR user define aggregate (part 2). Use multiple columns in the aggregation function
In part 1 we created a nice user defined aggregate. Now we are going to make it more sophisticated and let its value depend on two parameters ShipCountry and ShipShipCity. You might try having two parameters in Accumulate function of the aggregate but you will get an error
The Accumulate method in user defined aggregate "Bonus" must have exactly one parameter.
We are definitely looking into adding “multi-column aggregates” feature in the future versions of SQL Server. For now you can use a workaround. The idea is to create a worker UDT that contain all the fields required for the aggregation. So if you want to take Orders.ShipCountry and Orders.ShipCity into account the UDT should have two corresponding fields. You also need to create a user defined function that takes a number of parameters and returns an instance of the worker UDT. And finally you create an aggregate that takes the worker UDT as a parameter in its aggregation function.
Let’s say XYZ wants to consider German sales that has been shipped to Berlin as regular sales. To take ShipCity this into account you first need to create a UDT. I won’t implement several methods to keep the sample short.
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize=8000)]
public struct OrderData : INullable, IBinarySerialize
{
    public override string ToString()
    {
        throw new Exception("The method or operation is not implemented.");
    }
 
    public bool IsNull
    {
        get
        {
            return false;
        }
    }
 
 &n