日期:2014-05-17  浏览次数:20439 次

游标问题.大家看下我写的对么..
需求是输入参数,日期,和编号,查询指定编号一段时间内的数据.参数的作用是输入比例(例如10%.),用查询出的金额x比例再加上原查询的金额得出一个新的数据,而金额x比例得出的数据需要从现有查询金额里提取插入到查询结果里形成一条新的数据.
而插入的金额大于比例金额自动停止.
[code=SQL]ALTER   PROCEDURE     INTOCWXS
@bl                     float,
@begindate datetime,
@enddate                   datetime,
@subbh varchar(10)
as
begin
declare   @je   decimal(18,2)
declare   @srje   decimal(18,4)
declare   @mbje   decimal(18,4)
declare   @lsh   varchar(50),@kdrq   varchar(10);
set   @je=0
set   @mbje=0
select     @srje=sum(round(sl*sj,2))*round(@bl*0.01,2)   from   subfhdyibao   where   kdrq   between   @begindate   and   @enddate   and   subbh=@subbh   group   by   kdrq
declare   cs   cursor   for  
select   sum(round(sl*sj,2)),lsh,kdrq,subbh   from   subfhdyibao   where   kdrq   between   @begindate   and   @enddate   and   subbh=@subbh   group   by   lsh,kdrq,subbh
open   cs
fetch   next   from   cs   into   @je,@lsh,@kdrq,@subbh
while   @@fetch_status=0   --(@srje <@srje+round(@srje*0.1,2))
begin

fetch   next   from   cs   into   @je,@lsh,@kdrq,@subbh
set   @je=@je+@je
     
  insert   into   subfhdyibao(subbh,lsh,dh,kdrq,hh,sj,sl,czry)
  select   @subbh,lsh+ 'c ',dh+ 'c ',kdrq,hh,sj,sl,czry   from   subfhdyibao   where   @lsh=lsh   and   @kdrq=kdrq   and   subbh=@subbh  

print   @je
print   @lsh
print   @subbh
print   @kdrq
print   @srje
if   @je> @srje

break


        fetch   next   from   cs   into   @je,@lsh,@kdrq,@subbh
end

close   cs
deallocate   cs  
end[/code]
大家看这样写可以么...谢谢了.小弟新手..没什么分给大家...

------解决方案--------------------
将subfhdyibao表的结构贴一下.
------解决方案--------------------
使用一条SQL语句,筛选条件进行处理。