title: Stored Procedure always returning default value
body: <p>I have written a stored procedure for simple user Login function.</p>
  
  <pre><code>USE [project]
  GO
  /****** Object:  StoredProcedure [dbo].[authenticateLogin]    Script Date: 10/18/2013     9:24:57 AM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [dbo].[authenticateLogin] @userName varchar, @password varchar
  AS
  BEGIN
      Declare @userRole int;
      Declare @uPassword varchar;
          Declare @uRole int;
  
      SET @uPassword = (SELECT uPassword FROM [user] WHERE uName=@userName); 
  
  IF(@uPassword = @password)
      SET @userRole = (SELECT uRole FROM [user] WHERE uName=@userName); 
  ELSE
      SET @userRole = 0;
  --RETURN @userRole
  SELECT @userRole 
  END
  </code></pre>
  
  <p>And I am calling this from my program like this:</p>
  
  <pre><code>internal int Authenticate(string userName, string password)
      {
          int userRole = 0;
          Shared shrObj=new Shared();
          string encPassword = shrObj.EncryptToString(password);
          SqlConnection sqlCon = Shared.GetSqlCon();
  
          var sqlCom = new SqlCommand("authenticateLogin", sqlCon);
          sqlCom.CommandType = CommandType.StoredProcedure;           
  
          sqlCom.Parameters.AddWithValue("@userName", userName);
          sqlCom.Parameters.AddWithValue("@password", encPassword);
          try
          {
              userRole = Shared.ExecuteNonQueryOnProcedure(sqlCon, sqlCom);
              if (userRole &gt; 0) return userRole;
          }
          catch (SqlException sqlEx)
          {
              //catch
          }
  
          finally
          {
              Shared.COC(sqlCon);
          }
          return userRole;
      }
  </code></pre>
  
  <p>In <code>Shared.cs</code></p>
  
  <pre><code>public static int ExecuteNonQueryOnProcedure(SqlConnection sqlCon, SqlCommand sqlCom) {
  
          int rowCount = 0;
          SqlParameter returnParameter = sqlCom.Parameters.Add("userRole", SqlDbType.Int);
          returnParameter.Direction = ParameterDirection.ReturnValue;
  
          try
          {
              sqlCon.Open();
              //rowCount = sqlCom.ExecuteNonQuery();
              var inu = sqlCom.ExecuteScalar().ToString();
          }
          catch (Exception ex)
          {
              //catch
              return rowCount;
          }
          return (int)returnParameter.Value;
      }
  </code></pre>
  
  <p>But the issue is it always return me 0 even where provided arguments are matched. please help me where am i doing wrong.</p>