Wrong result of user defined aggregate in SQL Server

Question

I have been trying to create custom aggregate function in SQL Server. I finally got it working (wrote assembly in C#).

The goal is to concatenate all strings within a group, but the result is empty string.

Here is my class in C#:

[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 32)]
public struct STRING_CONCAT : IBinarySerialize
{
    //result - concatenated string
    SqlString result;

    public void Init()
    {
        //empty the result
        result = "";
    }

    public void Accumulate(SqlString value)
    {
        result += value;
    }

    public void Merge(STRING_CONCAT value)
    {
        Accumulate(value.Terminate());
    }

    public SqlString Terminate()
    {
        return result;
    }

    public void Read(BinaryReader r)
    {
        r.BaseStream.Position = 0;
        result = r.ReadString();
    }

    public void Write(BinaryWriter w)
    {
        w.Write(result.GetUnicodeBytes());
        w.BaseStream.Position = 0;
    }
}

And here is T-SQL part:

CREATE ASSEMBLY [SQLAggregate]
FROM 'C:\CSharp\SQLAggregate\SQLAggregate\bin\Debug\SQLAggregate.dll'
GO

create aggregate STRING_CONCAT
(@string nvarchar(100))
returns nvarchar(4000)
external name [SQLAggregate].[SQLAggregate.STRING_CONCAT]

Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. — Tab Alleman, Dec 26 '17 at 14:25
@TabAlleman expected output - concatenated strings. Actual output - empty string. — Evk, Dec 26 '17 at 14:44

score 0 · Accepted Answer · edited Dec 26 '17 at 14:44

0

Remove the setting of position on BinaryReader/BinaryWriter and change w.Write(result.GetUnicodeBytes()); to w.Write(result.Value);.

edited Dec 26 '17 at 14:44

Michał Turczyn

32,028
14
47
69

answered Dec 26 '17 at 14:27

TcKs

25,849
11
66
104

Then I get `EndOfStreamException` – Michał Turczyn Dec 26 '17 at 14:32
In that case. Write the string not unicode bytes. w.Write(result.Value): – TcKs Dec 26 '17 at 14:34

Wrong result of user defined aggregate in SQL Server

1 Answers1