CLR UDF returning Varbinary(MAX)

Question

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!

Answer 1

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).

Answer 2

It appears that the answer is yes - you can use both varbinary(MAX) by returning "SqlBinary" or you can use SqlBytes as recommended above.