CLR User-Defined Aggregate Functions

Download latest development release with samples from http://www.effiproz.com/downloads/effiproz_sl4_1_1_beta.zip

Following example shows how to create a CLR User-Defined Aggregate Function in EffiProz-SL database.

CLR aggregate function must have "System.Data.EffiProz.Clr.SqlUserDefinedAggregate" custom attribute and three standard methods "Init", "Accumulate" and "Terminate" as shown below (Which very similar to what is given for SQL server on http://msdn.microsoft.com/en-us/library/ms131056.aspx ),

[SqlUserDefinedAggregate]
public class Concatenate 
{ 
private StringBuilder intermediateResult;
public void Init()

{
this.intermediateResult = new StringBuilder();
}

public void Accumulate(String value)
{
if (value == null)

{

return;

}

this.intermediateResult.Append(value).Append('
');

}

public String Terminate()

{
string output = string.Empty;

if (this.intermediateResult != null

&& this.intermediateResult.Length > 0)

{

output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);

}

return output; 

}

}

Then we can create a new aggregate function with CREATE AGGREGATE statement,

[
CREATE AGGREGATE MyAgg (input varchar(200)) RETURNS varchar(100000)
 EXTERNAL NAME 'EffiProz.Test.SelfTest.Concatenate';

Now we can create a sample table and call the new aggregate function,

CREATE TABLE BookAuthors
 (
 BookID   int       NOT NULL,
 AuthorName    varchar(200) NOT NULL
 );

INSERT INTO BookAuthors VALUES(1, 'Johnson');
INSERT INTO BookAuthors VALUES(2, 'Taylor');
INSERT INTO BookAuthors VALUES(3, 'Steven');
INSERT INTO BookAuthors VALUES(2, 'Mayler');
INSERT INTO BookAuthors VALUES(3, 'Roberts');
INSERT INTO BookAuthors VALUES(3, 'Michaels');

SELECT BookID, public.MyAgg(AuthorName) FROM BookAuthors GROUP BY BookID;

Finally we can drop the aggregate with "DROP AGGREGATE" statement,

DROP AGGREGATE MyAgg;