谁帮我优化一下这个存储过程,把他改成不用游标的,很快揭帖
create procedure CommisionAll1
@endDate DateTime /*传入佣金日期*/
As
declare @Region nvarchar(6)
declare @points float
set @points=0
declare mycur CURSOR local for /*声明一个游标变量*/
select sum(计点数) as 佣金数,地区代理号
from 一级市场计点单登记表
where year(消费日期)=year(@endDate) and month(消费日期)=month(@endDate) and 标注2='是'
group by 地区代理号
open mycur
fetch next from mycur into @points,@Region /*取出两个变量,并把游标挪到下一个记录*/
while (@@Fetch_status=0)
begin
/*通过持卡人的卡号获取地区代理编号和一级市场剩余点数*/
insert into 一级市场地区佣金汇总表(地区代理号,佣金额,佣金日期,市场级别)
values(@Region,@points,@endDate,'1')
fetch next from mycur into @points,@Region /*游标下移*/
end
close mycur
deallocate mycur
go
问题点数：50、回复次数：1Top
1 楼zjcxc（邹建）回复于 2004-08-04 17:04:40 得分 50
create procedure CommisionAll1
@endDate DateTime /*传入佣金日期*/
As
insert into 一级市场地区佣金汇总表(地区代理号,佣金额,佣金日期,市场级别)
select 地区代理号,sum(计点数) as 佣金数,@endDate,'1'
from 一级市场计点单登记表
where year(消费日期)=year(@endDate) and month(消费日期)=month(@endDate) and 标注2='是'
group by 地区代理号
go
Top
