Reputation: 103477

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

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;

Upvotes: 231

Answers (5)

Reputation: 37

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.

Upvotes: 2

Reputation: 117

You do not need to pass the size parameter, just declare Varchar already understands that it is MAX like:

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

Upvotes: 0

Reputation: 255

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";

Upvotes: 6

Reputation: 761

For those of us who did not see -1 by Michal Chaniewski, the complete line of code:

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

Upvotes: 76

Reputation: 4806

In this case you use -1.

Upvotes: 350