DECLARE outhitch_cursor CURSOR FOR
select EndReason,count(EndReason) as counts from (select distinct d.LogID_No, d.ID_Day,d.EndReason from cdrlog l,cdrdetail d where l.id_no=d.logid_no and l.id_day=d.id_day + @condition_time + and l.stepcount=d.logstep and len(r.CallerDN)=4 and l.holdlong <>0 and l.CallerID <>'' and l.CalledID <>'' and right(d.CallerDN,3)=right(''+@ccu_extnumber+'',3) and l.isout=1) allitem group by EndReason order by EndReason
OPEN outhitch_cursor
FETCH NEXT FROM outhitch_cursor
INTO @EndReason, @EndReasonCount
WHILE @@FETCH_STATUS = 0
BEGIN
case @EndReason
when 2 then set @activehitch=@activehitch+@EndReasonCount
when 3 then set @passivehitch=@passivehitch+@EndReasonCount
else set @abnormityhitch=@abnormityhitch+@EndReasonCount
end
FETCH NEXT FROM outhitch_cursor
INTO @EndReason, @EndReasonCount
END
CLOSE outhitch_cursor
DEALLOCATE outhitch_cursor
小弟写了这么一段代码,但是无法执行。因为很少使用存储过程对编程规则不太清除,自己找不出错误原因。希望各位大大可以抽空帮忙修改一下。小弟先拜谢了。我想实现在存储过程中循环一个sql语句的查询结果。但是这个sql语句的部分条件是存在变量里的。如果我这样不能实现的话谢谢各位大大能给出个解决办法。小弟再次拜谢了。
select EndReason,count(EndReason) as counts from (select distinct d.LogID_No, d.ID_Day,d.EndReason from cdrlog l,cdrdetail d where l.id_no=d.logid_no and l.id_day=d.id_day + @condition_time + and l.stepcount=d.logstep and len(r.CallerDN)=4 and l.holdlong <>0 and l.CallerID <>'' and l.CalledID <>'' and right(d.CallerDN,3)=right(''+@ccu_extnumber+'',3) and l.isout=1) allitem group by EndReason order by EndReason
OPEN outhitch_cursor
FETCH NEXT FROM outhitch_cursor
INTO @EndReason, @EndReasonCount
WHILE @@FETCH_STATUS = 0
BEGIN
case @EndReason
when 2 then set @activehitch=@activehitch+@EndReasonCount
when 3 then set @passivehitch=@passivehitch+@EndReasonCount
else set @abnormityhitch=@abnormityhitch+@EndReasonCount
end
FETCH NEXT FROM outhitch_cursor
INTO @EndReason, @EndReasonCount
END
CLOSE outhitch_cursor
DEALLOCATE outhitch_cursor
小弟写了这么一段代码,但是无法执行。因为很少使用存储过程对编程规则不太清除,自己找不出错误原因。希望各位大大可以抽空帮忙修改一下。小弟先拜谢了。我想实现在存储过程中循环一个sql语句的查询结果。但是这个sql语句的部分条件是存在变量里的。如果我这样不能实现的话谢谢各位大大能给出个解决办法。小弟再次拜谢了。
DECLARE outhitch_cursor CURSOR FOR
select EndReason,count(EndReason) as counts from
(select distinct d.LogID_No, d.ID_Day,d.EndReason from cdrlog l,cdrdetail d
where l.id_no=d.logid_no and l.id_day=d.id_day + @condition_time + and l.stepcount=d.logstep
and len(r.CallerDN)=4 and l.holdlong <>0 and l.CallerID <>'' and l.CalledID <>''
and right(d.CallerDN,3)=right(''+@ccu_extnumber+'',3) and l.isout=1) allitem group by EndReason order by EndReason
OPEN outhitch_cursor
FETCH NEXT FROM outhitch_cursor
INTO @EndReason, @EndReasonCount
WHILE @@FETCH_STATUS = 0
BEGIN
select @activehitch =@activehitch +case @EndReason when 2 then @EndReasonCount else 0 end,
@passivehitch =@passivehitch +case @EndReason when 3 then @EndReasonCount else 0 end,
@abnormityhitch=@abnormityhitch+case when @EndReason not in(2,3) then @EndReasonCount else 0 end
end
FETCH NEXT FROM outhitch_cursor
INTO @EndReason, @EndReasonCount
END
CLOSE outhitch_cursor
DEALLOCATE outhitch_cursor
select EndReason,count(EndReason) as counts
from
(select distinct d.LogID_No, d.ID_Day,d.EndReason
from cdrlog l,cdrdetail d
where l.id_no=d.logid_no
and l.id_day=d.id_day + @condition_time + ----> and l.id_day=d.id_day @condition_time +
and l.stepcount=d.logstep
and len(r.CallerDN)=4
and l.holdlong <>0
and l.CallerID <>''
and l.CalledID <>''
and right(d.CallerDN,3)=right(''+@ccu_extnumber+'',3)
and l.isout=1)
allitem group by EndReason
order by EndReason
消息 156,级别 15,状态 1,过程 sp_Statistics,第 115 行
关键字 'case' 附近有语法错误。
消息 156,级别 15,状态 1,过程 sp_Statistics,第 117 行
关键字 'when' 附近有语法错误。
消息 156,级别 15,状态 1,过程 sp_Statistics,第 118 行
关键字 'else' 附近有语法错误。
消息 156,级别 15,状态 1,过程 sp_Statistics,第 124 行
关键字 'CLOSE' 附近有语法错误。
消息 156,级别 15,状态 1,过程 sp_Statistics,第 115 行
关键字 'case' 附近有语法错误。
消息 156,级别 15,状态 1,过程 sp_Statistics,第 117 行
关键字 'when' 附近有语法错误。
消息 156,级别 15,状态 1,过程 sp_Statistics,第 118 行
关键字 'else' 附近有语法错误。
消息 156,级别 15,状态 1,过程 sp_Statistics,第 124 行
关键字 'CLOSE' 附近有语法错误。[/Quote]
你改成if else 吧,