title: ExecuteScalar returns 0 executing a stored procedure
body: <p>I'm refactoring a C# program that calls a stored procedure that ends with:</p>
  
  <pre><code>SELECT @ResultCode AS ResultCode
  </code></pre>
  
  <p>The C# code looks like this:</p>
  
  <pre><code>SqlDbCommand.CommandType = System.Data.CommandType.StoredProcedure;
  SqlDbCommand.CommandText = "PR_Foo";
  
  SqlDbCommand.Parameters.Clear();
  SqlDbCommand.Parameters.Add("@Foo", SqlDbType.Char);  
  SqlDbCommand.Parameters["@Foo"].Value = 'Foo';
  
  System.Data.SqlClient.SqlDataAdapter SqlDbAdapter = new System.Data.SqlClient.SqlDataAdapter();
  System.Data.DataSet SQLDataSet = new System.Data.DataSet();
  SqlDbAdapter.SelectCommand = SqlDbCommand;
  SqlDbAdapter.Fill(SQLDataSet);
  SQLDataSet.Tables[0].TableName = "PR_Foo";
  
  if (SQLDataSet.Tables.Count != 0) {
         Result = int.Parse(SQLDataSet.Tables[SQLDataSet.Tables.Count - 1].Rows[0][0].ToString());
  }
  </code></pre>
  
  <p>With the above code, <code>Result</code> is correctly populated with the value returned by the<br>
  stored procedure.</p>
  
  <p>Refactoring the code with a simpler <code>ExecuteScalar</code>:</p>
  
  <pre><code>SqlDbCommand.CommandType = System.Data.CommandType.StoredProcedure;
  SqlDbCommand.CommandText = "PR_Foo";
  
  SqlDbCommand.Parameters.Clear();
  SqlDbCommand.Parameters.Add("@Foo", SqlDbType.Char); 
  SqlDbCommand.Parameters["@Foo"].Value = 'Foo';
  
  Result = (int)SqlDbCommand.ExecuteScalar(); 
  </code></pre>
  
  <p>the <code>Result</code> value is oddly set to 0 while the expected result should be an integer value greater than zero.</p>
  
  <p>Do you know what could be the cause of this strange behavior?</p>
  
  <p><em><strong>Note:</em></strong>  </p>
  
  <p>the stored procedure has several if blocks, returning result values lower than zero in case of particular checks; these cases are correctly handled by the ExecuteScalar().</p>
  
  <p>The problem raises when the stored procedure does its job correctly, committing the transactions of the various updates and returning the <code>Result</code> value at the end.  </p>