SQL Server CLR函数聚合的排序字符串

Question

为了获得排序的聚合字符串，我在下面编写了CLR函数。但是，它总是返回空值，而不是我的期望值，就像“ 001，002，003”。我试图在Visual Studio 2017中调试CLR功能，但抛出了错误消息

操作无法完成。未指定的错误

代码：

[Serializable]

[SqlUserDefinedAggregate(

    Format.UserDefined, //use clr serialization to serialize the intermediate result

    Name = "CLRSortedCssvAgg", //aggregate name on sql

    IsInvariantToNulls = true, //optimizer property

    IsInvariantToDuplicates = false, //optimizer property

    IsInvariantToOrder = false, //optimizer property

    IsNullIfEmpty = false, //optimizer property

    MaxByteSize = -1) //maximum size in bytes of persisted value

]

public class SortedCssvConcatenateAgg : IBinarySerialize

{

    /// <summary>

    /// The variable that holds all the strings to be aggregated.

    /// </summary>

    List<string> aggregationList;

    StringBuilder accumulator;

    /// <summary>

    /// Separator between concatenated values.

    /// </summary>

    const string CommaSpaceSeparator = ", ";

    /// <summary>

    /// Initialize the internal data structures.

    /// </summary>

    public void Init()

    {

        accumulator = new StringBuilder();

        aggregationList = new List<string>();

    }

    /// <summary>

    /// Accumulate the next value, not if the value is null or empty.

    /// </summary>

    public void Accumulate(SqlString value)

    {

        if (value.IsNull || String.IsNullOrEmpty(value.Value))

        {

            return;

        }

        aggregationList.Add(value.Value);

    }

    /// <summary>

    /// Merge the partially computed aggregate with this aggregate.

    /// </summary>

    /// <param name="other"></param>

    public void Merge(SortedCssvConcatenateAgg other)

    {

        aggregationList.AddRange(other.aggregationList);

    }

Answer 1

在Accumulate和中Merge，您正在处理您的aggregationList;&nbsp;进入Read，Write您正在处理accumulator。您应该为所有这些选择一个或另一个，然后使用它。按照我的理解，Read并Write在使用时，引擎需要坚持临时结果到工作表。对于您的情况，执行此操作时，它仅保留您的空StringBuilder。