How to create CLR stored procedure with Nvarchar(max) parameter?

Question

Is it possible to create CLR stored procedure in SQL Server CLR project having input parameterof type nvarchar(max)?

If you define stored procedure:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub MyProcedure(ByVal param1 As String)

Then when you deploy it, param1 is of type NVarchar(4000). Is there a way to have it NVarchar(max)?

Answer 1

You can use the SqlFacet attribute. If you want the NVARCHAR(MAX) type as a parameter, then you should do this:

[SqlProcedure]
public static void storedProcedure1([SqlFacet(MaxSize=-1)] String param){ .. }

If you need it as a return value in a user defined function:

[return:SqlFacet(MaxSize=-1)]
[SqlFunction]
public static String userFunction1(){ ... }

The MaxSize=-1 indicates that the size of the NVARCHAR will be MAX.

Answer 2

Define your parameter to be of type SqlChars, instead of string. See Handling Large Object Parameters in the CLR