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?

  • Agreed, @Paul. And I've been wondering how to get this to work - thanks for that answer! – Max Vernon Apr 16 '14 at 1:13

You need to specify a SqlFacet for the return value. This is the same as specifying a SqlFacet for an input parameter, except you prefix it with return:

For (max) length, use MaxSize=-1 in the SqlFacet attribute, example follows:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
    [return: SqlFacet(IsFixedLength=false, IsNullable=true, MaxSize=-1)] 
    public static SqlString ScalarFunction1
            [SqlFacet(IsFixedLength=false, IsNullable=true, MaxSize=-1)] 
            SqlString input
        return input;

The T-SQL script generated for this SQLCLR function is:

CREATE FUNCTION [dbo].[ScalarFunction1]
(@input NVARCHAR (MAX))
 EXTERNAL NAME [Library].[UserDefinedFunctions].[ScalarFunction1]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.