Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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
up vote 5 down vote accepted

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]
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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