存储过程总是返回 0 [英] Stored Procedure always returning 0

问题描述

我试图从存储过程中获取返回值，但它总是返回 0.

I am trying to get returned value from stored procedure, but it always returning 0.

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

在所有情况下，它都返回 0 .我无法弄清楚问题所在.

In all cases, it is returning 0 . i cant figure out the problem.

推荐答案

我尝试了与您类似的代码，它按预期工作.
但是，在您的代码中有两次对 ExecuteNonQuery 的调用.

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

当第一次调用时，记录按预期插入，然后添加返回值的参数并再次执行命令.但是现在记录存在并且存储过程总是落在 else 块中，因此总是返回零.

When the first call is made the record is inserted as expected, then you add the parameter for the return value and execute again the command. But now the record exists and the stored procedure falls always in the else block thus returning always zero.

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;

这篇关于存储过程总是返回 0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！