Multi-Parameter 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.
This is very similar to 2nd example given for SQL server on http://msdn.microsoft.com/en-us/library/ms131056.aspx

CLR aggregate function definition,

[SqlUserDefinedAggregate]
    public struct WeightedAvg
    {     
        private long sum;        
        private int count;

     
        public void Init()
        {
            sum = 0;
            count = 0;
        }

    
        public void Accumulate(int? Value, int? Weight)
        {
            if (Value.HasValue && Weight.HasValue)
            {
                sum += (long)Value.Value * (long)Weight.Value;
                count += (int)Weight.Value;
            }
        }    

      
        public int? Terminate()
        {
            if (count > 0)
            {
                int value = (int)(sum / count);
                return value;
            }
            else
            {
                return null;
            }
        }
    }

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

CREATE AGGREGATE WeightedAvg (val int, weight int) RETURNS int
 EXTERNAL NAME 'EffiProz.Test.SelfTest.WeightedAvg';

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

CREATE TABLE MyTable (ItemValue int, ItemWeight int);

INSERT INTO MyTable VALUES(1, 4);
INSERT INTO MyTable VALUES(6, 1);

SELECT public.WeightedAvg(ItemValue, ItemWeight) FROM MyTable;

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

DROP AGGREGATE WeightedAvg;