How to get return value from stored procedure

Question

I have the following stored procedure:

ALTER PROCEDURE spLogin
       @CAD_WEB_LOGIN_USERNAME varchar(60),
       @CAD_WEB_LOGIN_PASSWORD varchar(60),
       @Result int output
AS
BEGIN
    SELECT
        CAD_NUMBER 
    FROM 
        CMACADDR
    WHERE 
        CAD_WEB_LOGIN_USERNAME = @CAD_WEB_LOGIN_USERNAME 
        AND CAD_WEB_LOGIN_PASSWORD = @CAD_WEB_LOGIN_PASSWORD
END

In C#, I want to execute this query and get the return value.

This is my code:

int flag = 0;

con.Open();

SqlCommand cmd3 = new SqlCommand("spLogin", con);
cmd3.Connection = con;
cmd3.CommandType = CommandType.StoredProcedure;

cmd3.Parameters.Add("@CAD_WEB_LOGIN_USERNAME", SqlDbType.VarChar).Value = txtUserName.Text;
cmd3.Parameters.Add("@CAD_WEB_LOGIN_PASSWORD", SqlDbType.VarChar).Value = txtPassword.Text;

SqlParameter parm = new SqlParameter("@Return", SqlDbType.Int);
parm.Direction = ParameterDirection.ReturnValue;
cmd3.Parameters.Add(parm);

flag = cmd3.ExecuteNonQuery();

con.Close();
int id = Convert.ToInt32(parm.Value);

I get an error:

Procedure or function 'spLogin' expects parameter '@Result', which was not supplied.

What's the logic error with this code?

Thanks

Solution 1

As error suggest 'spLogin' expects parameter '@Result'

Change

SqlParameter parm = new SqlParameter("@Return", SqlDbType.Int);

to

SqlParameter parm = new SqlParameter("@Result", SqlDbType.Int);

EDIT

Also updated your procedure, return some value. Currently you are not returning anything. Also you don't need to add an extra parameter in SP.

ALTER PROCEDURE Splogin @CAD_WEB_LOGIN_USERNAME VARCHAR(60), 
                        @CAD_WEB_LOGIN_PASSWORD VARCHAR(60)
AS 
  BEGIN 
      Declare @MyResult as INT 
      SELECT @MyResult = cad_number 
      FROM   cmacaddr 
      WHERE  cad_web_login_username = @CAD_WEB_LOGIN_USERNAME 
             AND cad_web_login_password = @CAD_WEB_LOGIN_PASSWORD 

      RETURN @MyResult -- return value
  END 

Solution 2

Change the ParameterDirection to Output , and change the parameter name to @Result .

SqlParameter parm = new SqlParameter("@Result", SqlDbType.Int);

parm.Direction = ParameterDirection.Output;

cmd3.Parameters.Add(parm);

Comments

[Sara]

i have updated my SP and also from '@Return' to '@Result' but still same error. Thanks

[Sagar Chavan]

Perfect i am searching from last 4 hour its work fine thanks.