varchar(max) - SqlParameter

[zorba]

I'm wondering how should I set up SqlParameter
in order to pass varchar(max) type values.

http://msdn2.microsoft.com/en-us/library/bb399384.aspx
suggests using "-1" as value for SqlParameter's size.


so:

command.CommandText = "select len(@p)";
command.CommandType = CommandType.Text;
SqlParameter p = New SqlParameter("@p", SqlDbType.VarChar, -1);
p.Value = new String('a',10);
command.Parameters.Add(p)

After this code is executed several times (with
"@p" parameter holding string of different lengths),
it appears that this query is present in the
sys.dm_exec_cached_plans view multiple times
(each cache item is stored for concrete @p parameter
length).

It seems that:
- when using 0, -1 or simply not providind Size for SqlParameter,
it's size is calculated from parameter value.
- if it is above limits (4000/8000), the parameter
gets VarChar(MAX) type
- otherwise, parameter gets concrete VarChar(size) type.
- in order to make query optimizer reuse cached query plan,
datatypes must match (including size...).


So - back then - how to setup SqlParameter in order to
pass VarChar(MAX) values correctly?

 

[Cyborgx37]

If you are simply passing the value into a SQL Proc (etc), then you could use
AddWithValue and the type will be assumed based on the value you enter.

I think AddWithValue was added in VS 05.

 

[Nick]

Very interesting issue. I've just discovered and hesitantly started using
varchar(max) myself. I haven't pinned any problems onto it yet but your post
seems to prove my sceptisism about it.
Did you resolve the matter?