Search code examples
sql-serversqlclruser-defined-functions

CLR UDF returning Varbinary(MAX)

Is it possible for a SQL CLR User-Defined Function to return the data type varbinary(MAX)?

In the documentation it mentions:

"The input parameters and the type returned from a scalar-valued function can be any of the scalar data types supported by SQL Server, except rowversion, text, ntext, image, timestamp, table, or cursor." - they don't mention varbinary, but I'm not sure...

I have some byte-array data from the .NET side that I need to return to SQL Server from the CLR, and I'm trying to avoid having to do it with an output parameter from a stored procedure (this is how I have it working in test now).

Thanks!

Solution

  • If you define it as returning a SqlBytes data type, this should correctly map to varbinary(MAX) in SQL Server.

    [SqlFunction]
public static SqlBytes Function1()
{
    return new SqlBytes(Encoding.UTF8.GetBytes("Hello world."));
}

    Whilst you can also use the SqlBinary data type, if you deploy via Visual Studio, it will be mapped onto varbinary(8000) rather than varbinary(MAX).