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

The stored procedure defines a parameter @Result but in your C# code, you're calling it @Return - those names must match ! — marc_s
– marc_s, Commented Dec 23, 2013 at 12:02

Sarath Subramanian · Accepted Answer · 2015-04-12 07:37:48Z

4

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

edited Apr 12, 2015 at 7:37

Sarath Subramanian

21.6k12 gold badges89 silver badges95 bronze badges

answered Dec 24, 2013 at 9:03

Anas Al-Qudah

1291 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sagar Chavan Over a year ago

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

Satpal · Accepted Answer · 2013-12-23 12:35:38Z

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

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

Collectives™ on Stack Overflow

How to get return value from stored procedure

2 Answers 2

1 Comment

EDIT

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

EDIT

1 Comment

Related