returning int value from stored procedure and check it in asp.net code to validate login form

What is the true sequence to make this code run as I tried many time but I don't get a valid result

// the code of SQL stored procedure

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 = @us开发者_Go百科ername
Set @pass_word = @password

if EXISTS (select username , password from data where username= @user_name) 
set @result= 1
else 
set @result=0

return @result

and asp.net code is

 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();

You're executing .ExecuteScalar() so you're expecting back a result set with a single row, single column from the stored procedure - but you're not selecting anything at the end of your stored proc!

You need to change your last line in the stored proc from

return @result

to

SELECT @result

and then it should work.

Add another parameter for the return value

ALTER PROC [dbo].[login_proc] 
  @username Varchar = 50, 
  @password Varchar = 50,
  @result int OUTPUT

Examples can be viewd here.

Did you try this one; Use the

return  @result

and in c#

int resultID = Convert.ToInt32(cmd.ExecuteScalar());

Also remove next line

cmd.Parameters[""].value;

I'm unable to login any suggestions, both in case of stored procedure and codebehind can anyone provide solution to this,,,plz,,,