Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to return an nvarchar(max) in a CLR UDF?

Assuming following definition:

/// <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);
}

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).

like image 595
Daren Thomas Avatar asked Dec 10 '08 15:12

Daren Thomas


2 Answers

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);
}

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.

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.

like image 73
Daren Thomas Avatar answered Oct 22 '22 04:10

Daren Thomas


See also How to create CLR stored procedure with Nvarchar(max) parameter where you'll discover how/why you really should use the SqlChars data type. See Handling Large Object (LOB) Parameters in the CLR in MSDN.

like image 37
bielawski Avatar answered Oct 22 '22 06:10

bielawski