The stored procedure always returns 0

Question

I am trying to get the return value from a stored procedure, but always returns 0.

C # code

cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "AbsentEntry";
    cmd.Parameters.Add("@EmpID", SqlDbType.VarChar).Value = ViewState["empID"].ToString();
    cmd.Parameters.Add("@AttendanceDate", SqlDbType.Date).Value = date.ToString("yyyy-MM-dd");
    cmd.Connection = conn;
    conn.Open();
    cmd.ExecuteNonQuery();
    SqlParameter returnParameter = cmd.Parameters.Add("@returnval", SqlDbType.Int);
    returnParameter.Direction = ParameterDirection.ReturnValue;
    cmd.ExecuteNonQuery();

    int result = (int)cmd.Parameters["@returnval"].Value;
    return result;

SP

    ALTER PROCEDURE [dbo].[AbsentEntry] 
@EmpID varchar(10), 
@AttendanceDate Date 

AS BEGIN
declare @returnval     int 
IF (SELECT COUNT(*) FROM tblEmpAttendance WHERE EmpID = @EmpID AND AttendanceDate=@AttendanceDate) = 0
    BEGIN
        insert into tblEmpAttendance (EmpID, AttendanceDate, IsInOut, SessionCount, IsPresent) values ( @EmpID,@AttendanceDate,'OUT',0,'A')
        set @returnval=1
    return @returnval
     END
ELSE
    BEGIN
       set @returnval=0 
    return @returnval
    END
END

In all cases, it returns 0. I cannot understand the problem.

Answer 1

I tried a similar code similar to yours and it works as expected.
However, in your code there is a call to ExecuteNonQuery two times.

When the first call is made, the record is inserted as expected, then you add a parameter for the return value and run the command again. But now the record exists, and the stored procedure always falls into the else block, therefore, zero is always returned.

cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "AbsentEntry";
cmd.Parameters.Add("@EmpID", SqlDbType.VarChar).Value = ViewState["empID"].ToString();
cmd.Parameters.Add("@AttendanceDate", SqlDbType.Date).Value = date.ToString("yyyy-MM-dd");
SqlParameter returnParameter = cmd.Parameters.Add("@returnval", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
int result = (int)cmd.Parameters["@returnval"].Value;
return result;

Answer 2

, executononquery.

executescalar .

Answer 3

out , ExecuteNonQuery - , . # , . = GetX(); vs getX();

, ,

ALTER PROCEDURE [dbo].[AbsentEntry] 
    @EmpID varchar(10), 
    @AttendanceDate Date,
    @returnval     int OUTPUT

AS BEGIN

    IF (SELECT COUNT(*) FROM tblEmpAttendance 
        WHERE EmpID = @EmpID AND AttendanceDate=@AttendanceDate) = 0
    BEGIN
        insert into tblEmpAttendance (EmpID, AttendanceDate, IsInOut,
        SessionCount, IsPresent) values ( @EmpID,@AttendanceDate,'OUT',0,'A')
        set @returnval=1
    END
ELSE
    BEGIN
       set @returnval=0 
    END
END

, , ExecuteScalar .

int result = (int) cmd.ExecuteScalar()

Answer 4

count u execuitscaler..

int result=convert.toint32(cmd.executescalar());

int result = (Int32)cmd.ExecuteScalar();