title: returning int value from stored procedure and check it in asp.net code to validate login form
body: <p>What is the true sequence to make this code run as I tried many time but I don't get a valid result</p>
  
  <p>// the code of SQL stored procedure </p>
  
  <pre><code>set ANSI_NULLS ON
  set QUOTED_IDENTIFIER ON
  GO
  ALTER PROC [dbo].[login_proc] @username Varchar =50, @password varchar=50
  as
  Declare @user_name varchar , @pass_word varchar, @result varchar 
  Set @user_name = @username
  Set @pass_word = @password
  
  if EXISTS (select username , password from data where username= @user_name) 
  set @result= 1
  else 
  set @result=0
  
  return @result
  </code></pre>
  
  <p>and asp.net code is </p>
  
  <pre><code> SqlConnection conn = new SqlConnection ("Data Source=ANAGUIB-LAPNEW\\SQLEXPRESS;Initial Catalog=account;Integrated Security=True");
          SqlCommand cmd = new SqlCommand("login_proc",conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter paramReturnValue = new SqlParameter();
            paramReturnValue.ParameterName = "@result";
             paramReturnValue.SqlDbType = SqlDbType.Int;
             cmd.Parameters.Add(paramReturnValue);
             cmd.Parameters["@result"].Direction = ParameterDirection.Output;
             conn.Open();
  
           cmd.Parameters.AddWithValue("@username", TextBox1.Text);
           cmd.Parameters.AddWithValue("@password", TextBox2.Text);
           cmd.ExecuteScalar();
           string retunvalue = (string)cmd.Parameters["@result"].Value;
  
           if (retunvalue == "1")
          {
              Response.Redirect("hello.aspx");
          }
          else 
          {
              Response.Write("error");
          }
  
          conn.Close();
  </code></pre>