存储过程返回小数问题

[理想2017]

这是存储过程
CREATE PROCEDURE x_class

(
@area varchar(40),
@xqid int,
@xqid2 int,
@grade int,
@subject int,
@teacher int,
@classroom varchar(40)
)
AS
declare @sql nvarchar(2000)
declare @rece nvarchar(2000)
declare @rece22 nvarchar(2000)
declare @coun int
declare @coun2 int
declare @classlv numeric(16,2)
declare @sb int
select @sql='select @a=count(DISTINCT xjcode) FROM xueji_bmjf WHERE jxzq ='+cast(@xqid as varchar)
select @rece='select @b=count(DISTINCT xjcode) FROM xueji_bmjf WHERE jxzq ='+cast(@xqid2 as varchar)+' and xjcode in(select DISTINCT xjcode FROM xueji_bmjf
WHERE jxzq ='+cast(@xqid as varchar)+')'

if @area<>''
begin
set @sql=@sql+' and charindex('''+@area+''',xueji_bmjf.area)>0'
set @rece=@rece+' and charindex('''+@area+''',xueji_bmjf.area)>0'
end

if @grade<>0
begin
set @sql=@sql+' and xueji_bmjf.s_grade='+cast(@grade as varchar)
set @rece=@rece+' and xueji_bmjf.s_grade='+cast(@grade as varchar)
end

if @subject<>0
begin
set @sql=@sql+' and xueji_bmjf. jf_subject='+cast(@sb as varchar)
set @rece=@rece+' and xueji_bmjf. jf_subject='+cast(@sb as varchar)
end

if @teacher<>0
begin
set @sql=@sql+' and xueji_bmjf.teacher='+cast(@teacher as varchar)
set @rece=@rece+' and xueji_bmjf.teacher='+cast(@teacher as varchar)
end

if @classroom<>''
begin
set @sql=@sql+' and xueji_bmjf.classroom='''+@classroom+''''
set @rece=@rece+' and xueji_bmjf.classroom='''+@classroom+''''
end
--print @sql
--print @rece
exec sp_executesql @sql,N'@a int output',@coun output
exec sp_executesql @rece,N'@b int output',@coun2 output
set @classlv=convert(numeric(16,2),cast(@coun2 as float) /cast(@coun as float))
return @classlv
GO

这是返回函数

public decimal getReturnValue(string storedProcName, IDataParameter[] parameters)
{
SqlConnection conn;
conn = new SqlConnection(ConnStr);
conn.Open();

SqlCommand cmd = new SqlCommand(storedProcName, conn);
cmd.CommandType = CommandType.StoredProcedure;

foreach (SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
//cmd.Parameters[parameter].Direction = ParameterDirection.Input;
}

SqlParameter returnvalue = cmd.Parameters.Add("@returnvalue", SqlDbType.Decimal);
returnvalue.Direction = ParameterDirection.ReturnValue;
returnvalue.Precision = (byte)16;
returnvalue.Scale = (byte)2;

cmd.ExecuteNonQuery();



decimal fhvalue = Convert.ToDecimal(cmd.Parameters["@returnvalue"].Value);

return fhvalue;


}

我用 x_class '',42,43,0,0,0,''测试过能够返回小数
可是现在返回的值是0 请问高手这里应该怎样改呢

[理想2017]

CREATE PROCEDURE x_class

(
@area varchar(40),
@xqid int,
@xqid2 int,
@grade int,
@subject int,
@teacher int,
@classroom varchar(40),
@returnvalue numeric(16,2)=4.44 output
)
AS
declare @sql nvarchar(2000)
declare @rece nvarchar(2000)
declare @rece22 nvarchar(2000)
declare @coun float
declare @coun2 float
declare @classlv numeric(16,2)
declare @return_classlv varchar(40)
declare @sb int
select @sql='select @a=count(DISTINCT xjcode) FROM xueji_bmjf WHERE jxzq ='+cast(@xqid as varchar)
select @rece='select @b=count(DISTINCT xjcode) FROM xueji_bmjf WHERE jxzq ='+cast(@xqid2 as varchar)+' and xjcode in(select DISTINCT xjcode FROM xueji_bmjf
WHERE jxzq ='+cast(@xqid as varchar)+')'

if @area<>''
begin
set @sql=@sql+' and charindex('''+@area+''',xueji_bmjf.area)>0'
set @rece=@rece+' and charindex('''+@area+''',xueji_bmjf.area)>0'
end

if @grade<>0
begin
set @sql=@sql+' and xueji_bmjf.s_grade='+cast(@grade as varchar)
set @rece=@rece+' and xueji_bmjf.s_grade='+cast(@grade as varchar)
end

if @subject<>0
begin
set @sql=@sql+' and xueji_bmjf. jf_subject='+cast(@sb as varchar)
set @rece=@rece+' and xueji_bmjf. jf_subject='+cast(@sb as varchar)
end

if @teacher<>0
begin
set @sql=@sql+' and xueji_bmjf.teacher='+cast(@teacher as varchar)
set @rece=@rece+' and xueji_bmjf.teacher='+cast(@teacher as varchar)
end

if @classroom<>''
begin
set @sql=@sql+' and xueji_bmjf.classroom='''+@classroom+''''
set @rece=@rece+' and xueji_bmjf.classroom='''+@classroom+''''
end

exec sp_executesql @sql,N'@a int output',@coun output
exec sp_executesql @rece,N'@b int output',@coun2 output
set @returnvalue=4.42

GO
set @returnvalue=4.42 直接赋值这样也返回0
我感觉应该是
public decimal getReturnValue(string storedProcName, IDataParameter[] parameters)
{
SqlConnection conn;
conn = new SqlConnection(ConnStr);
conn.Open();

SqlCommand cmd = new SqlCommand(storedProcName, conn);
cmd.CommandType = CommandType.StoredProcedure;

foreach (SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
//cmd.Parameters[parameter].Direction = ParameterDirection.Input;
}

SqlParameter returnvalue = cmd.Parameters.Add("@returnvalue", SqlDbType.Decimal);
returnvalue.Direction = ParameterDirection.ReturnValue;
returnvalue.Precision = (byte)16;
returnvalue.Scale = (byte)2;

cmd.ExecuteNonQuery();



decimal fhvalue = Convert.ToDecimal(cmd.Parameters["@returnvalue"].Value);

return fhvalue;


}
这个有问题啊，请各位帮我看看这个返回函数应该怎样改呢

[好奇都是要学的]

哪快设计到 fhvalue了 ， ， 就在他下面print 1 print 2这样自己就能排错了， 看看0 是在哪得的

或者你可能跟有 NULL 加在一起了， 我一看你这长度， 就不想看代码了， 太长了。。 按我说的，自己排错吧

[理想2017]

[Quote=引用 9 楼 wang2129929 的回复:]
哪快设计到 fhvalue了 ， ， 就在他下面print 1 print 2这样自己就能排错了， 看看0 是在哪得的

或者你可能跟有 NULL 加在一起了， 我一看你这长度， 就不想看代码了， 太长了。。 按我说的，自己排错吧
[/Quote]
能否说得详细些啊？我用存储过程 x_class '',42,43,0,0,0,''测试过能够返回小数
测试没有问题的了，为什么通过这个方法在页面返回就有错误呢

[myhope88]

那就不用这种方法去输出吧，你用个输出参数来返回这个结果吧

[骑猪看海]

是啊！！！！

[hanbingnertel]

用一个Output类型的参数来输出返回值

[shazhibox]

学习中。。

[理想2017]

CREATE PROCEDURE x_class

(
@area varchar(40),
@xqid int,
@xqid2 int,
@grade int,
@subject int,
@teacher int,
@classroom varchar(40),
@returnvalue numeric(16,2)=0.00 output
)
AS
declare @sql nvarchar(2000)
declare @rece nvarchar(2000)
declare @rece22 nvarchar(2000)
declare @coun int
declare @coun2 int
declare @classlv numeric(16,2)
declare @return_classlv varchar(40)
declare @sb int
select @sql='select @a=count(DISTINCT xjcode) FROM xueji_bmjf WHERE jxzq ='+cast(@xqid as varchar)
select @rece='select @b=count(DISTINCT xjcode) FROM xueji_bmjf WHERE jxzq ='+cast(@xqid2 as varchar)+' and xjcode in(select DISTINCT xjcode FROM xueji_bmjf
WHERE jxzq ='+cast(@xqid as varchar)+')'

if @area<>''
begin
set @sql=@sql+' and charindex('''+@area+''',xueji_bmjf.area)>0'
set @rece=@rece+' and charindex('''+@area+''',xueji_bmjf.area)>0'
end

if @grade<>0
begin
set @sql=@sql+' and xueji_bmjf.s_grade='+cast(@grade as varchar)
set @rece=@rece+' and xueji_bmjf.s_grade='+cast(@grade as varchar)
end

if @subject<>0
begin
set @sql=@sql+' and xueji_bmjf. jf_subject='+cast(@sb as varchar)
set @rece=@rece+' and xueji_bmjf. jf_subject='+cast(@sb as varchar)
end

if @teacher<>0
begin
set @sql=@sql+' and xueji_bmjf.teacher='+cast(@teacher as varchar)
set @rece=@rece+' and xueji_bmjf.teacher='+cast(@teacher as varchar)
end

if @classroom<>''
begin
set @sql=@sql+' and xueji_bmjf.classroom='''+@classroom+''''
set @rece=@rece+' and xueji_bmjf.classroom='''+@classroom+''''
end
exec sp_executesql @sql,N'@a int output',@coun output
exec sp_executesql @rece,N'@b int output',@coun2 output
set @returnvalue=convert(numeric(16,2),cast(@coun2 as float) /cast(@coun as float))
GO

public decimal getReturnValue(string storedProcName, IDataParameter[] parameters)
{
SqlConnection conn;
conn = new SqlConnection(ConnStr);
conn.Open();

SqlCommand cmd = new SqlCommand(storedProcName, conn);
cmd.CommandType = CommandType.StoredProcedure;

foreach (SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
//cmd.Parameters[parameter].Direction = ParameterDirection.Input;
}

SqlParameter returnvalue = cmd.Parameters.Add("@returnvalue", SqlDbType.Decimal);
returnvalue.Direction = ParameterDirection.ReturnValue;
returnvalue.Precision = (byte)16;
returnvalue.Scale = (byte)2;

cmd.ExecuteNonQuery();



decimal fhvalue = Convert.ToDecimal(cmd.Parameters["@returnvalue"].Value);

return fhvalue;


}

这样也是0的啊
是不是我的返回函数写错啦

[yangchun1213]

路过，每天回帖，加10分

[teerhu]

declare @count float
declare @coun2 float

[liyangyang621726]

没运行到哪 print下
看问题出在哪

[理想2017]

ParameterDirection.Output; 这个忘记写了

[理想2017]

ParameterDirection.Output; 这个忘记写了