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