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