I have a SQLCLR stored procedure with the following signature in Visual Studio 2013:
[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlString ExecSql2Json(SqlString sql)
Or alternatively I've tried:
[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlChar ExecSql2Json(SqlChar sql)
Either way the parameter and return type gets defined as NVARCHAR(4000)
. I'd like them to be NVARCHAR(MAX)
.
I know I can manually write my own CREATE PROCEDURE
statement to have fine grain control. Is there any option anywhere to change the deploy DDL generated by SQL Server or do I just have to manually add a script to drop and read the UDF with the proper signature?
Best Answer
You need to specify a
SqlFacet
for the return value. This is the same as specifying aSqlFacet
for an input parameter, except you prefix it withreturn:
For
(max)
length, useMaxSize=-1
in theSqlFacet
attribute, example follows:The T-SQL script generated for this SQLCLR function is: