如何在CLR UDF返回一个nvarchar（最大值）？ [英] How to return an nvarchar(max) in a CLR UDF?

问题描述

假设如下定义：

/// <summary>
/// Replaces each occurrence of sPattern in sInput with sReplace. This is done 
/// with the CLR: 
/// new RegEx(sPattern, RegexOptions.Multiline).Replace(sInput, sReplace). 
/// The result of the replacement is the return value.
/// </summary>
[SqlFunction(IsDeterministic = true)]
public static  SqlString FRegexReplace(string sInput, string sPattern, 
      string sReplace)
{
    return new Regex(sPattern, RegexOptions.Multiline).Replace(sInput, sReplace);
}

传递一个为nvarchar（最大），长度> 4000，将导致价值 sInput 值被截断（即调用这个UDF是 nvarchar的结果（4000 ），而不是为nvarchar（最大）。

Passing in a nvarchar(max) value for sInput with a length > 4000 will result in the value being truncated (i.e. the result of calling this UDF is nvarchar(4000) as opposed to nvarchar(max).

推荐答案

呵呵，不管，我发现自己的答案：

Oh, whatever, I found the answer myself:

/// <summary>
/// Replaces each occurrence of sPattern in sInput with sReplace. This is done 
/// with the CLR: 
/// new RegEx(sPattern, RegexOptions.Multiline).Replace(sInput, sReplace). 
/// The result of the replacement is the return value.
/// </summary>
[SqlFunction(IsDeterministic = true)]
[return: SqlFacet(MaxSize = -1)]
public static  SqlString FRegexReplace([SqlFacet(MaxSize = -1)]string sInput, 
       string sPattern, string sReplace)
{
    return new Regex(sPattern, RegexOptions.Multiline).Replace(sInput, sReplace);
}

理念是暗示到SQL Server的输入和返回值是不是默认的为nvarchar（4000），但有不同的尺寸。

The idea is to hint to SQL Server that the input and return values are not the default nvarchar(4000), but have a different size.

我学到关于属性的新招数：他们可以被添加到参数以及方法本身（相当明显），但也的返回值与 [返回：为AttributeName（参数=值，...）。语法

I learned a new trick regarding attributes: They can be added to the parameters as well as the method itself (quite obvious), but also to the return value with the [return: AttributeName(Parameter=Value, ...)] Syntax.

这篇关于如何在CLR UDF返回一个nvarchar（最大值）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！