T-SQL (varchar(max) vs CLR (string, SqlString, SqlChars)?

Question

I have a problem, that I can't solve. I'm using SQL Server 2005, C# CLR for using outer dll. The problem is at length of parameter. I need to use as function parameter type varchar(max). If at C# code I use string, SqlSring, I can't use T-SQL type varchar(max), just varchar(4000) of nvarchar(4000). I need to say, that can be situations, when I need to use more then 4000 symbols, so I need know, what C# type I need to use for varchar(max).

I have read a lot of articles, and several of them say, that for this I can use SqlChars. But! I have manipulations with strings. How can I have actions with string or SqlString and then convert to SqlChars? (It is possible SqlChars.ToString() or SqlChars.ToSqlString()).

I didn't find any C# code for this.

Answer 1

What you need to do is add SqlFacet(MaxSize = -1) attribute to parameter:

[return: SqlFacet(MaxSize = -1)]
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
public static SqlString YourSqlFunction([SqlFacet(MaxSize = -1)]SqlString sourceSS)
{ return new }