急，我的存储过程错在哪里，感觉死循环了。

Question

CREATE PROCEDURE GETCOPYPREPDATA
AS
DECLARE @iCount INT,
        @SumQuantityForAVG DECIMAL(25,4),
        @AvgThreeQuantity DECIMAL(25,4),
        @TheQuantity DECIMAL(25,4),
        @CopyYear int,
        @CopyMonth int,
        @ID int,
        @UserSN varchar,
        @MmNumber varchar,
        @UserName varchar,
        @LinkAddr varchar,
        @LinkMan varchar,
        @LinkTel varchar,
        @MmCalibre varchar,
        @MbPerson varchar,
    @UDPName varchar,
        @Price decimal(18,2),
        @CopyState varchar,
        @CurrentMeterRecord DECIMAL(25,4),
        @MeterBookPos int,
        @customerMeterID intBEGIN DECLARE CopyNoteData_PrepMath CURSOR FOR
select a.ID,b.UserSN,g.MmNumber,b.UserName,b.LinkAddr,b.LinkMan,b.LinkTel,g.MmCalibre,h.MbPerson,
f.UDPName,sum(d.ChargePrice) as Price,'未抄' as CopyState,a.CurrentMeterRecord,a.MeterBookPos
from wcUserWaterMeter a 
inner join wcUserBaseInfo b on a.UserID=b.ID
inner join wcAccountFee c on b.ID=c.UserMeterID
inner join wcChargePrice d on c.FeeTypeID=d.ID
inner join wcListUDP e on d.KindID=e.ID
inner join wcListUDP f on e.ParentID=f.ID
inner join wcMeterManage g on a.MeterID=g.ID
inner join wcMeterBook h on a.MeterBookID=h.ID
where a.MeterID>0 and b.IsCancelAccount<>1 and h.MbNumber='06160020001'
group by a.ID,b.UserSN,g.MmNumber,b.UserName,b.LinkAddr,b.LinkMan,b.LinkTel,g.MmCalibre, 
h.MbPerson,f.UDPName,a.CurrentMeterRecord,a.MeterBookPos open CopyNoteData_PrepMath
fetch next from CopyNoteData_prepMath into 
    @ID,@UserSN,@MmNumber,@UserName,@LinkAddr,@LinkMan,@LinkTel,@MmCalibre,@MbPerson,
@UDPName,@Price,@CopyState,@CurrentMeterRecord,@MeterBookPos
while @@Fetch_status=0
begin 
DECLARE CopyNoteData_ThreeMonthCursor CURSOR FOR
select top 3 customerMeterID,sum(TheQuantity) TheQuantity,CopyYear,CopyMonth from wcCopyNoteData
where CustomerMeterID=@ID
group by customerMeterID,CopyYear,CopyMonth 
order by CopyYear desc,CopyMonth desc open CopyNoteData_ThreeMonthCursor
fetch next from CopyNoteData_ThreeMonthCursor into @customerMeterID,@TheQuantity,@CopyYear,@CopyMonth
while @@Fetch_status=0
begin 
   set @iCount=@@CURSOR_ROWS
   set @SumQuantityForAVG = @SumQuantityForAVG + @TheQuantity              
end
if @iCount>0
 begin
set @AvgThreeQuantity = @SumQuantityForAVG/@iCount
 end
 else
 begin
set @AvgThreeQuantity = 0
 end INSERT INTO  wcCopyPrepMath VALUES (
@UserSN,@MmNumber,@UserName,
@LinkAddr,@LinkMan,@LinkTel,
@MmCalibre,@MbPerson,@UDPName,
@Price,@CopyState,@CurrentMeterRecord,
@MeterBookPos,@AvgThreeQuantity,2009,7
)
endEND

Answer 1

1.内层的while没有fetch next的动作。
2.没有销毁游标的语句。

Answer 2

少了fetch next，当然死循环

Answer 3

--try:
CREATE PROCEDURE GETCOPYPREPDATA
AS
DECLARE @iCount INT,
        @SumQuantityForAVG DECIMAL(25,4),
        @AvgThreeQuantity DECIMAL(25,4),
        @TheQuantity DECIMAL(25,4),
        @CopyYear int,
        @CopyMonth int,
        @ID int,
        @UserSN varchar,
        @MmNumber varchar,
        @UserName varchar,
        @LinkAddr varchar,
        @LinkMan varchar,
        @LinkTel varchar,
        @MmCalibre varchar,
        @MbPerson varchar,
        @UDPName varchar,
        @Price decimal(18,2),
        @CopyState varchar,
        @CurrentMeterRecord DECIMAL(25,4),
        @MeterBookPos int,
        @customerMeterID intBEGIN    DECLARE CopyNoteData_PrepMath CURSOR FOR
    select a.ID,b.UserSN,g.MmNumber,b.UserName,b.LinkAddr,b.LinkMan,b.LinkTel,g.MmCalibre,h.MbPerson,
    f.UDPName,sum(d.ChargePrice) as Price,'未抄' as CopyState,a.CurrentMeterRecord,a.MeterBookPos
    from wcUserWaterMeter a 
    inner join wcUserBaseInfo b on a.UserID=b.ID
    inner join wcAccountFee c on b.ID=c.UserMeterID
    inner join wcChargePrice d on c.FeeTypeID=d.ID
    inner join wcListUDP e on d.KindID=e.ID
    inner join wcListUDP f on e.ParentID=f.ID
    inner join wcMeterManage g on a.MeterID=g.ID
    inner join wcMeterBook h on a.MeterBookID=h.ID
    where a.MeterID>0 and b.IsCancelAccount<>1 and h.MbNumber='06160020001'
    group by a.ID,b.UserSN,g.MmNumber,b.UserName,b.LinkAddr,b.LinkMan,b.LinkTel,g.MmCalibre, 
    h.MbPerson,f.UDPName,a.CurrentMeterRecord,a.MeterBookPos    open CopyNoteData_PrepMath
    fetch next from CopyNoteData_prepMath into 
    @ID,@UserSN,@MmNumber,@UserName,@LinkAddr,@LinkMan,@LinkTel,@MmCalibre,@MbPerson,
    @UDPName,@Price,@CopyState,@CurrentMeterRecord,@MeterBookPos
    while @@Fetch_status=0
    begin 
        DECLARE CopyNoteData_ThreeMonthCursor CURSOR FOR
        select top 3 customerMeterID,sum(TheQuantity) TheQuantity,CopyYear,CopyMonth from wcCopyNoteData
        where CustomerMeterID=@ID
        group by customerMeterID,CopyYear,CopyMonth 
        order by CopyYear desc,CopyMonth desc        open CopyNoteData_ThreeMonthCursor
        fetch next from CopyNoteData_ThreeMonthCursor into @customerMeterID,@TheQuantity,@CopyYear,@CopyMonth
        while @@Fetch_status=0        
        begin 
               set @iCount=@@CURSOR_ROWS
               set @SumQuantityForAVG = @SumQuantityForAVG + @TheQuantity    
   fetch next from CopyNoteData_ThreeMonthCursor into @customerMeterID,@TheQuantity,@CopyYear,@CopyMonth                                       
        end
deallocate CopyNoteData_ThreeMonthCursor
        if @iCount>0
         begin
            set @AvgThreeQuantity = @SumQuantityForAVG/@iCount
         end
         else
         begin
            set @AvgThreeQuantity = 0
         end        INSERT INTO  wcCopyPrepMath VALUES (
        @UserSN,@MmNumber,@UserName,
        @LinkAddr,@LinkMan,@LinkTel,
        @MmCalibre,@MbPerson,@UDPName,
        @Price,@CopyState,@CurrentMeterRecord,
        @MeterBookPos,@AvgThreeQuantity,2009,7
        ) fetch next from CopyNoteData_prepMath into 
@ID,@UserSN,@MmNumber,@UserName,@LinkAddr,@LinkMan,@LinkTel,@MmCalibre,@MbPerson,
@UDPName,@Price,@CopyState,@CurrentMeterRecord,@MeterBookPos
    end
deallocate CopyNoteData_ThreeMonthCursor
END
go

Answer 4

CREATE PROCEDURE GETCOPYPREPDATA
AS
DECLARE @iCount INT,
        @SumQuantityForAVG DECIMAL(25,4),
        @AvgThreeQuantity DECIMAL(25,4),
        @TheQuantity DECIMAL(25,4),
        @CopyYear int,
        @CopyMonth int,
        @ID int,
        @UserSN varchar,
        @MmNumber varchar,
        @UserName varchar,
        @LinkAddr varchar,
        @LinkMan varchar,
        @LinkTel varchar,
        @MmCalibre varchar,
        @MbPerson varchar,
        @UDPName varchar,
        @Price decimal(18,2),
        @CopyState varchar,
        @CurrentMeterRecord DECIMAL(25,4),
        @MeterBookPos int,
        @customerMeterID intBEGIN    DECLARE CopyNoteData_PrepMath CURSOR FOR
    select a.ID,b.UserSN,g.MmNumber,b.UserName,b.LinkAddr,b.LinkMan,b.LinkTel,g.MmCalibre,h.MbPerson,
    f.UDPName,sum(d.ChargePrice) as Price,'未抄' as CopyState,a.CurrentMeterRecord,a.MeterBookPos
    from wcUserWaterMeter a 
    inner join wcUserBaseInfo b on a.UserID=b.ID
    inner join wcAccountFee c on b.ID=c.UserMeterID
    inner join wcChargePrice d on c.FeeTypeID=d.ID
    inner join wcListUDP e on d.KindID=e.ID
    inner join wcListUDP f on e.ParentID=f.ID
    inner join wcMeterManage g on a.MeterID=g.ID
    inner join wcMeterBook h on a.MeterBookID=h.ID
    where a.MeterID>0 and b.IsCancelAccount<>1 and h.MbNumber='06160020001'
    group by a.ID,b.UserSN,g.MmNumber,b.UserName,b.LinkAddr,b.LinkMan,b.LinkTel,g.MmCalibre, 
    h.MbPerson,f.UDPName,a.CurrentMeterRecord,a.MeterBookPos    open CopyNoteData_PrepMath
    fetch next from CopyNoteData_prepMath into 
    @ID,@UserSN,@MmNumber,@UserName,@LinkAddr,@LinkMan,@LinkTel,@MmCalibre,@MbPerson,
    @UDPName,@Price,@CopyState,@CurrentMeterRecord,@MeterBookPos
    while @@Fetch_status=0
    begin 
        DECLARE CopyNoteData_ThreeMonthCursor CURSOR FOR
        select top 3 customerMeterID,sum(TheQuantity) TheQuantity,CopyYear,CopyMonth from wcCopyNoteData
        where CustomerMeterID=@ID
        group by customerMeterID,CopyYear,CopyMonth 
        order by CopyYear desc,CopyMonth desc        open CopyNoteData_ThreeMonthCursor
        fetch next from CopyNoteData_ThreeMonthCursor into @customerMeterID,@TheQuantity,@CopyYear,@CopyMonth
        while @@Fetch_status=0        
        begin 
               set @iCount=@@CURSOR_ROWS
               set @SumQuantityForAVG = @SumQuantityForAVG + @TheQuantity             
               fetch next from CopyNoteData_ThreeMonthCursor into @customerMeterID,@TheQuantity,@CopyYear,@CopyMonth
               
        end
        if @iCount>0
         begin
            set @AvgThreeQuantity = @SumQuantityForAVG/@iCount
         end
         else
         begin
            set @AvgThreeQuantity = 0
         end        INSERT INTO  wcCopyPrepMath VALUES (
        @UserSN,@MmNumber,@UserName,
        @LinkAddr,@LinkMan,@LinkTel,
        @MmCalibre,@MbPerson,@UDPName,
        @Price,@CopyState,@CurrentMeterRecord,
        @MeterBookPos,@AvgThreeQuantity,2009,7
        )    fetch next from CopyNoteData_prepMath into 
    @ID,@UserSN,@MmNumber,@UserName,@LinkAddr,@LinkMan,@LinkTel,@MmCalibre,@MbPerson,
    @UDPName,@Price,@CopyState,@CurrentMeterRecord,@MeterBookPos
    endEND

Answer 5

上面最后一句写错了：
deallocate CopyNoteData_ThreeMonthCursor--->deallocate CopyNoteData_prepMath 

Answer 6

close和deallocate 游标动作少了

Answer 7

最好先把
deallocate CopyNoteData_ThreeMonthCursor 
deallocate CopyNoteData_prepMath单独执行一下。

Answer 8

 if @iCount>0
         begin
            set @AvgThreeQuantity = @SumQuantityForAVG/@iCount
         end
         else
         begin
            set @AvgThreeQuantity = 0
         end
这段中的@AvgThreeQuantity ，@SumQuantityForAVG，@iCount
为什么会全部丢掉？