none
Strange results in SQL 2005 with CLR UDF and Varbinary(MAX) RRS feed

  • Question

  • I have an empty C# CLR UDF that looks like this:

    public static SqlBytes MyUDF(SqlBytes data) { return data; }

    Defined like this:

    CREATE FUNCTION [dbo].[MyUDF](@data [varbinary](max)) RETURNS [varbinary](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [MyAssembly].[UserDefinedFunctions].[MyUDF];

    Now I issue this query: select MyUDF(cast('' as varbinary(max))

    In SQL 2005 and SQL 2008 it returns 0x as expected. However in SQL 2012 it returns NULL!

    Is this a bug or a feature of SQL 2012? How do you return a zero length varbinary from a CLR UDF in SQL 2012?

    A similar thing happens with a nvarchar UDF.


    Friday, February 7, 2014 8:16 PM

Answers

  • On 2008 R2 this returned NULL for me

    set nocount on 
    select dbo.MyUDF(cast('' as varbinary(max) ))
    
    select @@version

    NULL

     

    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)

    Jun 28 2012 08:36:30

    Copyright (c) Microsoft Corporation

    Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

    And I was able to return a zero-length varbinary with this function:

    public static byte[] MyUDF(SqlBytesdata) { return new byte[0]; }

    David


    David http://blogs.msdn.com/b/dbrowne/


    Friday, February 7, 2014 10:06 PM

All replies

  • On 2008 R2 this returned NULL for me

    set nocount on 
    select dbo.MyUDF(cast('' as varbinary(max) ))
    
    select @@version

    NULL

     

    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)

    Jun 28 2012 08:36:30

    Copyright (c) Microsoft Corporation

    Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

    And I was able to return a zero-length varbinary with this function:

    public static byte[] MyUDF(SqlBytesdata) { return new byte[0]; }

    David


    David http://blogs.msdn.com/b/dbrowne/


    Friday, February 7, 2014 10:06 PM
  • And I was able to return a zero-length varbinary with this function:

    public static byte[] MyUDF(SqlBytesdata) { return new byte[0]; }

    Thanks for the suggestion. Interesting. I didn't realize that the C# code could be declared as
    byte[] instead of SqlBytes.  How would I do the same thing for a zero length nvarchar?


    Saturday, February 8, 2014 2:03 PM
  • Please keep in mind, that such UDF will be unable to handle more than 8000 bytes of data.

    Proper solution is 

    [return: Microsoft.SqlServer.Server.SqlFacet(MaxSize = -1)]
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBinary myFunction()

    ....

    return new SqlBinary(new byte[] {});

    • Edited by Kazzman Wednesday, October 2, 2019 1:24 PM
    • Proposed as answer by Kazzman Wednesday, October 2, 2019 1:24 PM
    Wednesday, October 2, 2019 12:40 PM