Databases
StackOverflow2013
Postsdbo
POWeird behavior with two output parameters in a stored proc called from c#
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWeird behavior with two output parameters in a stored proc called from c#
    primarykey
    data
    text
    <p>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:</p> <pre><code>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()); </code></pre> <p>Just to clarify, this parameter was declared as OUTPUT in the stored proc but as inputoutput in the C# code. This worked fine.</p> <p>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:</p> <pre><code> 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); </code></pre> <p>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.</p> <pre><code>SqlParameter Var2Param = new SqlParameter("@Var2", 0); Var2Param.Direction = ParameterDirection.Output; cmd.Parameters.Add(Var2Param); </code></pre> <p>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.</p> <p>Now the execution</p> <pre><code> // 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()); </code></pre> <p>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.</p> <p>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?</p> <hr> <p>I will point out that I'm using executenonquery because the SP does an update. sarfeast is using execute scalar. I'm assuming that this shouldn't make any difference but I'll ask if anyone has any ideas on why this is happening. I modified the code by specifying sizes for the sqlparameters and now the value for var2 is showing up in var1 and var2 is Null!!!</p> <p>Any advice would be appreciated.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMarc Gravell
    1. USNeil Newton
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWeird behavior with two output parameters in a stored proc called from c#
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe code the you've listed looks fair enough to me, you can certainly use parameters in this way to return values from the SQL. However you've only posted half of the code, the other half being in the Stroed Procedure. If you could post the SQL that would help identify the problem.
      singulars
      1. POWeird behavior with two output parameters in a stored proc called from c#
      1. USJustABitOfCode
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload