What size do you use for varchar(MAX) in your parameter declaration?

Question

I normally set my column size when creating a parameter in ADO.NET.

But what size do I use if the column is of type VARCHAR(MAX)?

cmd.Parameters.Add("@blah", SqlDbType.VarChar, ?????).Value = blah;

user_119800 · Answer

In this case you use -1.

user_4269707 · Answer

The maximum SqlDbType.VarChar size is 2147483647.

If you would use a generic oledb connection instead of sql, I found here there is also a LongVarChar datatype. Its max size is 2147483647.

cmd.Parameters.Add("@blah", OleDbType.LongVarChar, -1).Value = "very big string";

user_8938409 · Answer

If you do something like this:

    cmd.Parameters.Add("@blah",SqlDbType.VarChar).Value = "some large text";

size will be taken from "some large text".Length

This can be problematic when it's an output parameter, you get back no more characters then you put as input.