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