C# InputOutput Parameter - Explain This

  • Thread starter Thread starter Darrell Unger
  • Start date Start date
D

Darrell Unger

Q: Why does a varchar output parameter need to be explicitly set when an int
output parameter can be set implicitly?

We have been successfully writing methods to return InputOutput parameters
of type int using the following syntax:

....
SqlConnection conn = new SqlConnection (ConnectionString);
SqlCommand cmd = new SqlCommand ("sp_dothis", conn);

string resultTemp;

conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Param1", strParam1);
cmd.Parameters.Add("@Param2", intParam2);
cmd.Parameters.Add("@ParamOut", intParmOut);

cmd.ExecuteNonQuery();
resultTemp = cmd.Parameters["@SourceResult"].Value.ToString();
conn.Close();
....

However, if the output parameter is of type varchar, the above syntax will
not return a value or throw an error -- it just silently returns a
null/empty value. To make a similar proc to work with a varchar value the
following is required:

....
SqlConnection conn = new SqlConnection (ConnectionString);
SqlCommand cmd = new SqlCommand ("sp_dothis", conn);

string resultTemp;

conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Param1", strParam1);
cmd.Parameters.Add("@Param2", intParam2);

// Be explicit when setting the varchar output parameter.
cmd.Parameters.Add("@ParamOut", SqlDbType.VarChar, 150);
cmd.Parameters["@SourceResult"].Direction = ParameterDirection.InputOutput;
cmd.Parameters["@SourceResult"].Value = SqlString.Null;

cmd.ExecuteNonQuery();
resultTemp = cmd.Parameters["@SourceResult"].Value.ToString();
conn.Close();
....

So, why does a varchar output parameter need to be explicitly set when an
int output parameter can be set implicitly?
 
Back
Top