Search code examples
t-sqlado.netvarbinarysqlparameter

Is it correct to use -1 as a size for an output SqlParameter to retrieve a VARBINARY(max) value?


I have a stored procedure with an OUTPUT parameter of the varbinary(MAX) type:

ALTER PROCEDURE [dbo].[StoredProcedure1]
  ...
  @FileData varbinary(MAX) OUTPUT
AS
...

I don't know what would be the actual size of the returned data, so I can't use an exact value for the size parameter of the SqlParameter constructor. From the other hand, the actual size could be more than 8 Kb (if it matters).

When I create a SqlParameter without declaring a size:

        var fileDataParameter = new SqlParameter("@FileData", SqlDbType.VarBinary)
            { Direction = ParameterDirection.Output };

        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(fileDataParameter);

        command.ExecuteNonQuery();

        var fileData = fileDataParameter.Value as byte[];

I'm getting the following exception on the command.ExecuteNonQuery() line:

Additional information: Byte[][0]: the Size property has an invalid size of 0.

So I need to specify the size. Some people recommend to pass -1 as the value of the size:

            var fileDataParameter = new SqlParameter("@FileData", SqlDbType.VarBinary, -1)
                { Direction = ParameterDirection.Output };

But I can't find a solid description of this thing neither on the MSDN page, nor anywhere else.

In my case, the maximum size of data returned in @FileData parameter isn't more than 10 Mb.

So the question is if passing -1 as a size to a SqlParameter mapped to a varbinary(MAX) OUTPUT parameter is the correct approach, for example, from the performance perspective?


Solution

  • Here is the MSDN documentation. https://msdn.microsoft.com/en-us/library/bb399384.aspx - See "Using Large Value Type Parameters" section

    Passing a "-1" is the correct approach for "MAX" value size. Since it's a VarChar, it won't add add or return any extra chars, only the ones you set in that column. So it should be very efficient.