Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
2  
Note to close-voters: this is a SQLCLR question with a SQL Server-specific answer. Seems on-topic here. –  Paul White Apr 11 at 17:33
    
Agreed, @Paul. And I've been wondering how to get this to work - thanks for that answer! –  Max Vernon Apr 16 at 1:13

1 Answer 1

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
{
    [SqlFunction
        (
        DataAccess=DataAccessKind.None,
        SystemDataAccess=SystemDataAccessKind.None,
        IsDeterministic=true,
        IsPrecise=true
        )
    ]
    [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))
RETURNS NVARCHAR (MAX)
AS
 EXTERNAL NAME [Library].[UserDefinedFunctions].[ScalarFunction1]
share|improve this answer

Your Answer

 
discard

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.