I 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.
User defined aggregates gives you more flexibility in terms of where you can implement complex business rules. Now you have all the option: client, middle tier and server. You cannot pass several columns into the aggregation function but you can work it around using UDTs.