Weird behavior with stored proc with two output parameters

• neilnewton001

  Mr or Mrs. 500

  Points: 503

  More actions

  March 29, 2011 at 8:53 am

  #237551

  Hi,

  I've modified an existing strored procedure. It originally had one ouput parameter in the stored proc and it was set up as follows in the c# code that called it:

  cmd.Parameters.Add("@Var1Param", SqlDbType.BigInt);

  if (Var1 == 0)

  cmd.Parameters["@Var1"].Value = DBNull.Value;

  else

  cmd.Parameters["@Var"].Value = Var1;

  cmd.Parameters["@Var1"].Direction = ParameterDirection.InputOutput;

  cmd.ExecuteNonQuery();

  // Get Var1

  Var1= dataMorph.ToInt64(cmd.Parameters["@Var1"].Value.ToString());

  Just to clarify, this parameter was declared as OUTPUT in the stored proc but as inputoutput in the C# code. This worked fine.

  I have added another parameter to the same stored procedure and I want to retrieve both from the C# code. The C# code now is as follows:

  SqlParameter Var1Param;

  if (VAR1 == 0)

  Var1Param = new SqlParameter("@Var1", DBNull.Value);

  else

  Var1Param = new SqlParameter("@Var1",Var1) ;

  Var1Param.Direction = ParameterDirection.InputOutput ;

  cmd.Parameters.Add(Var1Param);

  Again this parameter is declared as output in the stored proc but inputoutput in the c# code.

  Here is the new parameter. This is declared as output in the stored procedured. Here is the C# code.

  SqlParameter Var2Param = new SqlParameter("@Var2", 0);

  Var2Param.Direction = ParameterDirection.Output;

  cmd.Parameters.Add(Var2Param);

  I only want to output the second parameter as you can see. The assignment of 0 to this parameter seems to be a standard practice despite the fact it's an output variable only. I'm not sure if need to give it a type.

  Now the execution

  // Execute

  cmd.ExecuteScalar();

  connection.Close();

  // Get DonationID

  Var1 = dataMorph.ToInt64(cmd.Parameters["@Var1"].Value.ToString());

  //Get Supersession Daf ID return param to see if new DAF was created

  Var2 = dataMorph.ToInt64(cmd.Parameters["@Var2"].Value.ToString());

  What happens is that both the var1 and vaf2 parameters contain the value that was assigned to @Var2 parameter in the stored procedure. I have read that any number of parameters and parameter types can be used but it seems like the value assigned to @Var2 is overwriting the Value that had been passed in or assigned to Var1.

  Any advice? I know I can work around this by having multiple result sets and putting my output values in the second result set but I'd rather not deal with Kludges unless I have to. I've seen a lot of posts about the odd state of parameters after they return from stored procs and are accessed in c#. Is this type of behavior a bug?

  Thanks,

  Neil

• LutzM

  SSC Guru

  Points: 107049

  More actions

  March 29, 2011 at 10:42 am

  #1305024

  Are you sure this qustion belongs to a SQL forum? :ermm:

  Probably better to ask in a C# forum...

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• neilnewton001

  Mr or Mrs. 500

  Points: 503

  More actions

  March 29, 2011 at 11:28 am

  #1305048

  Lutz,

  I had considered that point. This is one of those fiew cases where it's hard to know where the problem is coming from. If this an issue of what sql server is expecting in the way of parameters and what it's passing back to c# and I've messed that up somehow then where this post should be is up in the air from my point of view. If sql server is doing what I expect and it's the way C# is handling data from the SP then you are right. I'm simply not certain.

  Thanks,

  Neil