Creating a CLR user define aggregate (part 1). Simple CLR aggregate

Published Mar 23 2019 04:23 AM 121 Views
Microsoft
First published on MSDN on Jun 06, 2006

SQL Server 2005 allows creating of User Defined Aggregate in any of the .NET  languages such as C# or VB. For simple cases like SUM or MAX you probably want  to use built-in aggregates, however there are cases  where build-ins are insufficient. In such cases people used to put the business logic on a client on a middle tier. With the new version of SQL Server you can have this logic on a server.


Let’s say company XYZ wants to come up with a way of calculating a bonus for their employees. XYZ uses NWIND database (NWIND database can be downloaded from http://msdn2.microsoft.com/en-us/library/ms143221.aspx ). XYZ wants to have a business rule such that the bonus is never greater than 200% of the salary and each regular sale adds 1% to the bonus and each sale to Germany adds 3% to the bonus.


With the new Sql Server 2005 you can write your own aggregates in C# (or any .NET compatible language). Here is the aggregate.


[Serializable]


[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]


public struct Bonus


{


private int m_nRegularSales;


private int m_nGermanSales;



public void Init()


{


m_nRegularSales = 0;


m_nGermanSales = 0;


}



public void Accumulate(SqlString Country)


{


if (Country == "Germany")


{


++m_nGermanSales;


}


else


{


++m_nRegularSales;


}


}



public void Merge(Bonus Group)


{


m_nRegularSales += Group.m_nRegularSales;


m_nGermanSales += Group.m_nGermanSales;


}



public int Terminate()


{


return Math.Min(200, (m_nRegularSales + 3 * m_nGermanSales));


}


}


And here is a T-SQL query that uses this aggregate to calculate bonus for each employee.


select


Employees.FirstName, Employees.LastName, dbo.Bonus(Orders.ShipCountry)


from


Employees join Orders on Employees.EmployeeId = Orders.EmployeeId


group by


Employees.EmployeeId, Employees.FirstName, Employees.LastName

Version history
Last update:
‎Mar 23 2019 04:23 AM
Updated by: