如何避免无限循环

Question

获取一个无限循环。游标获取一条记录用于测试，但不能离开获取循环。这是我第一次使用microsoftsql。我不确定fetch语句的语法和位置。我正在尝试更新发票详细信息，即一张发票有多个项目。请帮忙。

Begin--1
Declare     @Det_inv_cnt int,       ---------count invoice in detail
@Cnt_item5 int,         --------count item  number 5        
@Cnt_item6 int,         --------count item number 6
@sprice money,          --------sum vatable amt     
@vat_amt money,         --------vat amt 
@inv int,               --------invoice
@customer int,          --------customer id
@sdate datetime,        --------date
@edate datetime         --------date
begin--2

Declare inv_id Cursor For (Select invoice from plat.dbo.iheader 
Where customer in (Select id from plat.dbo.customer   
Where custype = 1 
and id = 2601)
and invoice = 2628 
OPEN inv_id
FETCH NEXT FROM inv_id INTO @inv -----------------------------next invoice from cursor
WHILE @@FETCH_STATUS=0 
BEGIN--------begin4
set @sprice      = 0;
set @vat_amt     = 0;
set @det_inv_cnt = 0;   
set @cnt_item5   = 0;
set @cnt_item6   = 0;
set @customer    = (select  customer from plat.dbo.iheader where @inv = invoice);
set @Det_inv_cnt = (select  count(1)
from    plat.dbo.idetail d  
where   d.item in ('2','4') 
and     d.invoice = @inv)

If (@Det_inv_cnt>0) ---------------------------------------end if 10
Begin -----begin5 end if 10
--calculate vatable amt and vat  round(0.15*sum(a.price),2)
set @sprice =   (Select  sum(price)     
From plat.dbo.idetail 
Where  invoice = @inv
And item in ('2','4'));
set @vat_amt = (Select  (0.15*(sum(price)))    
From plat.dbo.idetail 
Where  invoice = @inv
And item in ('2','4'));
set @cnt_item5 =    (Select count(1)
From    plat.dbo.idetail 
where   item     = '5'
and     invoice  = @inv
and     customer = @customer);
set @sdate =       (select  sdate  
From    idetail     
Where   item ='2'
or      item = '4'
And     invoice = @inv);
set @edate =        (select edate 
From   idetail     
Where  item ='2'
or      item = '4'
And     invoice = @inv);
--select details to insert new record
INSERT INTO idetail
( invoice 
, item 
, descriptio 
, qty 
, price 
, date 
, customer 
, sdate 
, edate  
--,timestamp
,profileid
, id_item_code 
, id_taxable_amt 
, id_item_instock 
, id_extended 
, id_category_id 
, id_subcategory_id 
, id_rg_id 
, id_svc_id 
, id_sbd_id 
, id_rate_id 
, id_prorated )
values ( @inv                           --invoice
,'TAX#1003'                         --item
,'#VAT (Rs. '+ @sprice +' x 15%)'   --descriptio
,1                                  --qty
,@vat_amt                           --price
,@sdate                                 --date
,@customer                          --customer
,@sdate                             --sdate
,@edate                             --edate
--,CURRENT_TIMESTAMP
,-1                                 --profileid
,0                                  --itemcode
,@SPRICE                            --taxable  amt
,null                               --id_item_instock, int,
,@VAT_AMT                           --id_extended, money,
,1002                               --id_category_id, int,
,0                                  --id_subcategory_id, int,
,0                                  --id_rg_id, int,
,0                                  --id_svc_id, int,
,0                                  --id_sbd_id, int,
,0                                  --id_rate_id, int,
,null);                             --id_prorated, char(1)
/*From  dbo.plat.idetail    
Where   item ='2'
or      item = '4'
And     invoice = @inv; 
*/

Begin --11
If @Cnt_item5 > 0 ------------------------------------------------------if cnt_item 5 >0
Begin------6
--delete  old VAT record
Delete from idetail 
where   invoice = @inv
and     item = '5'
and     customer = @customer;

--update roundoff item number 6
set @cnt_item6 =    (Select  count(1)
  from    plat.dbo.idetail w
  where   w.invoice = @inv
  and     w.customer = @customer
  and     w.item = '6')

If @Cnt_item6>0  -------------------------------------------if cnt_item6>0
Begin---7   
update plat.dbo.idetail 
set item = '5'
where   invoice = @inv
and     customer = @customer
and     item = '6';
End;--7
Else -------------------------------------------------------------else cnt_item6
Begin--8
Insert into  err_log( 
  El_customer,
  El_Invoice,
  El_msg,
  El_date)
Values (
  @customer,
  @inv,
  ('Residential Script - Count of Item 6 returned 0'),
  getdate());
End;--8
End; --end 6 of begin cnt5>0
End;--11
--  End;---5
---update header with vat_amt
Begin--9        
Update plat.dbo.iheader 
Set taxtotal    = @vat_amt
where invoice   = @inv
and customer    = @customer;
end;--9
end;--5
Else ----------else 10 dev_inv_cnt
/* no item found the  insert in table*/
begin--10
Insert into  err_log( 
El_customer,
El_Invoice,
El_msg,
El_date)
Values (
@customer
,@inv  
,('Residential - Count of Item 2 and 4 returned 0')
,Getdate());
end;--10
--End; --------end begin 4 while 
FETCH NEXT FROM inv_id INTO @inv --get nxt record for cursor  invt id       
End; ----begin 4
CLOSE inv_id;
DEALLOCATE inv_id;
--GO
end;---begin 2
End;--1

Answer 1

FETCH NEXT FROM Inv_IdINTO @invnext指令在前一个IF语句的ELSE分支中。它需要放在ELSE分支之后

Answer 2

您只从游标获取一次，而while循环正在检查该获取的状态。由于它可能成功了，所以这个条件总是被评估为真的。

值得一提的是，以下是我用于光标的模板：

declare cursorName cursor fast_forward local for
-- your select goes here
OPEN cursorName
WHILE (1=1)
BEGIN
FETCH NEXT FROM cursorName INTO --variable list here
if (@@FETCH_STATUS <> 0)
BREAK;
-- do stuff here 
END
CLOSE cursorName
DEALLOCATE cursorName