存储过程错误求助
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
