Search code examples
c#sqlstored-proceduressqlparameteroutput-parameter

Output SqlParameter Error: the Size property has an invalid size of 0

I know there are a few other threads/questions about this error, but I wanted to get a bit more insight if possible. We've seen this error occur intermittently for a particular piece of code where we have an Integer type output parameter.

This is how we're defining the parameter:

SqlParameter errorParam = new SqlParameter("@ErrorCode", errorCode);
errorParam.Direction = ParameterDirection.Output;

You'll notice no size and no DBType is specified. It seems the error doesn't always occur, but sometimes this will be thrown:

the Size property has an invalid size of 0. at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc) at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)

I'm assuming I need to add the DBType and Size settings, but what might be underlying reason be that this is occurring, and only in some occasions?

Note, the parameter in SQL is defined as:

@ErrorCode              INT                 OUT ,
Solution

  • The size is needed if the type is string, your type is int. You need to specify SqlDbType.Int and you'll avoid this issue all together. Here's why you should always specify the type.

    In your case since you didn't specify the type maybe .net thinks its a string, hence it is looking for the size property. It is best to be explicit, if you know the type provide it, don't rely on some automatic process that might guess it.

    In your case you can do this:

    SqlParameter errorParam = new SqlParameter("@ErrorCode", errorCode);
errorParam.SqlDbType = SqlDbType.Int;
errorParam.Direction = ParameterDirection.Output;

    Since it is an int you don't need to specify the size.