日期:2014-05-19  浏览次数:20473 次

存储过程错误求助
CREATE   PROCEDURE   eip_Report(
@reportowner   int, --输入:用户ID
@StartTime   datetime, --输入:开始时间
@EndTime   datetime                                               --输入:结束时间
)
AS
declare   @test   as   bigint
Begin   Tran
BEGIN
  Select   [reportowner],[StatTime],@test=(sum([MtSucceedNum])   as   MtAll),sum([MtErorNum])   AS   MtEro,SuM([MoNum])   AS   MOALL   from   eip_ReportNote
  where   [StatTime] <datediff(hh, '2007-07-01   00:00:00 ',@EndTime)   and   [StatTime]> DATEDIFF(hh, '2007-07-01   00:00:00 ',@StartTime)   and   [reportowner]=@reportowner
  insert   into   eip_test(StatTime)   values(@test)
END
commit
GO

我想把(sum([MtSucceedNum])   as   MtAll)求和这个值付给变量@test
但是出错了如何改正?谢谢

------解决方案--------------------
Select [reportowner],[StatTime],@test=sum([MtSucceedNum]),sum([MtErorNum]) AS MtEro,SuM([MoNum]) AS MOALL from eip_ReportNote

------解决方案--------------------
sum([MtSucceedNum])查询出来的值必须唯一.
--去掉as MtAll
--把除变量赋值外去掉
------解决方案--------------------
Select [reportowner],[StatTime],@test=(sum([MtSucceedNum]) as MtAll),.....
---------------------------------------
如果在select语句中使用了赋值语句@test = ...则不能再选择列,只能这样:
Select @test=sum([MtSucceedNum]) from table....
改为:
Select @test=sum([MtSucceedNum]) from eip_ReportNote
where [StatTime] <datediff(hh, '2007-07-01 00:00:00 ',@EndTime) and [StatTime]> DATEDIFF(hh, '2007-07-01 00:00:00 ',@StartTime) and [reportowner]=@reportowner
insert into eip_test(StatTime) values(@test)


可以简化为:
insert into eip_test(StatTime)
Select sum([MtSucceedNum]) from eip_ReportNote
where [StatTime] <datediff(hh, '2007-07-01 00:00:00 ',@EndTime) and [StatTime]> DATEDIFF(hh, '2007-07-01 00:00:00 ',@StartTime) and [reportowner]=@reportowner