Getting return value from stored procedure in C#

Question

I have the following query:

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 

This compiles perfectly fine. I want to execute this query and get the return value. My code is below:

  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; 

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?

Answer 1

Mehrdad makes some good points, but the main thing I noticed is that you never run the query...

SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar); retval.Direction = ParameterDirection.ReturnValue; sqlcomm.ExecuteNonQuery(); // MISSING string retunvalue = (string)sqlcomm.Parameters["@b"].Value;