CODEWITHSUNDEEP
sql-servertypessqldatatypessqlclr
Konstantin Nizhegorodov
Konstantin Nizhegorodov

Reputation: 61

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

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

Upvotes: 6

Views: 5416

Answers (2)

Guillermo Guti&#233;rrez
Guillermo Guti&#233;rrez

Reputation: 17869

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.

Upvotes: 9

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

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

Upvotes: 6

Related Questions