title: Getting return value from stored procedure in C#
body: <p>I have the following query:</p>
  
  <pre><code>set ANSI_NULLS ON
  set QUOTED_IDENTIFIER ON
  go
  
  ALTER PROCEDURE [dbo].[Validate]
  @a varchar(50),
  @b varchar(50) output
  
  AS
  
  SET @Password = 
  (SELECT Password
  FROM dbo.tblUser
  WHERE Login = @a)
  
  RETURN @b
  GO
  </code></pre>
  
  <p>This compiles perfectly fine. I want to execute this query and get the return value. My code is below:</p>
  
  <pre><code>  SqlConnection SqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyLocalSQLServer"].ConnectionString.ToString());
          System.Data.SqlClient.SqlCommand sqlcomm = new System.Data.SqlClient.SqlCommand("Validate", SqlConn);
  
          string returnValue = string.Empty;
  
          try
          {
              SqlConn.Open();
              sqlcomm.CommandType = CommandType.StoredProcedure;
  
              SqlParameter param = new SqlParameter("@a", SqlDbType.VarChar);
              param.Direction = ParameterDirection.Input;
              param.Value = Username;
              sqlcomm.Parameters.Add(param);
  
  
  
              SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
              retval.Direction = ParameterDirection.ReturnValue;
  
  
              string retunvalue = (string)sqlcomm.Parameters["@b"].Value;
  </code></pre>
  
  <p>Note: Exception handling cut to keep the code short. Everytime I get to the last line, null is returned. What's the logic error with this code?</p>