6

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

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
Konstantin Nizhegorodov
  • 61
  • 1
  • 4

2 Answers2

8

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.

Guillermo Gutiérrez
  • 17,273
  • 17
  • 89
  • 116
6

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

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Hi Damien. Through no fault of your own, this answer is now obsolete and meaningless. It was only that early versions of Visual Studio / SSDT had defaulted `SqlChars` to map to `NVARCHAR(MAX)` and `SqlString` to map to `NVARCHAR(4000)`. Starting in at least VS 2012, I think both `SqlString` and `SqlChars` map to `NVARCHAR(MAX)`. Also, even in those early days, the preferred method of indicating the T-SQL datatype options was via the `SqlFacet` attribute (as shown in [Guillermo's answer](https://stackoverflow.com/a/8748786/577765)). – Solomon Rutzky May 03 '18 at 23:56