title: Get the Default RETURN VALUE from a stored procedure?
body: <pre><code>public int Test()
          {
              int result = 1;
  
              SqlCommand cmd = new SqlCommand("spTest", conn);
              cmd.CommandType = CommandType.StoredProcedure;
  
              var returnParameter = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
              returnParameter.Direction = ParameterDirection.ReturnValue;
  
              try
              {
                  conn.Open();
                  cmd.ExecuteNonQuery();
                  result = Convert.ToInt32(returnParameter.Value);
              }
              catch (Exception e)
              {
                  MessageBox.Show(e.Message.ToString());
              }
              finally
              {
                  conn.Close();
              }
  
              return result;
          }
  </code></pre>
  
  <p>The method starts out with result = 1 so I can see it changing to a 0 if the query is successful. I tested this and it does change from 1 to a 0. My question is, is this the correct way to get the default RETURN_VALUE from a stored procedure?<br></p>
  
  <p>This also changed the value of result from 1 to 0. Why? The query hasn't run yet.</p>
  
  <pre><code>public int Test()
          {
              int result = 1;
  
              SqlCommand cmd = new SqlCommand("spTest", conn);
              cmd.CommandType = CommandType.StoredProcedure;
  
              var returnParameter = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
              returnParameter.Direction = ParameterDirection.ReturnValue;
  
              result = Convert.ToInt32(returnParameter.Value);
  
              try
              {
                  conn.Open();
                  cmd.ExecuteNonQuery();
              }
              catch (Exception e)
              {
                  MessageBox.Show(e.Message.ToString());
              }
              finally
              {
                  conn.Close();
              }
  
              return result;
          }
  </code></pre>