游标换成循环，，，谢谢
declare Level1 cursor for
select companyid,dptid,dptname,capcode,oldcode,storplace_id,storplace,deptype,years,months,buydate,usedate,chief,chiefcode,examiner,paymethod,
rowid,Capcode,Capname,round(orivalue/qty,2) as price, qty,round(depvalue/qty,2) as depvalue,round(salvalue/qty,2) as salvalue,orivalue,depvalue,memo,usetype,
usercode,username
from VfCapdocA with (nolock)
where doccode=@doccode
select @CardNo=0
open Level1
fetch next from Level1 into @companyid,@dptid,@dptname,@capcode,@oldcode,@storplace_id,@storplace,@deptype,@years,@months,@buydate,@docdate,@chief,@chiefcode,@examiner,
@paymethod,@rowid,@Capcode,@Capname,@price,@digit,@depvalue,@salvalue,@allorivalue,@alldepvalue,@memo,@usetype,@usercode,@username
while @@fetch_status=0
begin
set @i=1
while @i<=@digit
begin
update FaCardNoA
set CardNo=isnull(cardno,0)+1
where Capcode=@Capcode
if @@rowcount=0
begin
insert into FaCardNoA(Capcode,CardNo) values(@Capcode,1)
end
select @CardNo=CardNo from FaCardNoA where Capcode=@Capcode
select @cardid=@Capcode+
case when @CardNo>=1 and @CardNo<=9 then '000'+convert(varchar(1),@CardNo)
when @CardNo>=10 and @CardNo<=99 then '00'+convert(varchar(2),@CardNo)
when @CardNo>=100 and @CardNo<=999 then '0'+convert(varchar(3),@CardNo)
when @cardno>=1000 and @cardno<=9999 then convert(varchar(4),@cardno)
else 'OK'
end
if right(@cardid,2)='OK'
begin
close Level1
deallocate Level1
raiserror('超出编码长度!',16,1)
return
end
if @i=@digit
begin
set @price=isnull(@allorivalue,0)-(@digit-1)*isnull(@price,0)
set @depvalue=isnull(@alldepvalue,0)-(@digit-1)*isnull(@depvalue,0)
end
select @oppocompanyid=isnull(oppocompanyid,''),@oppocompanyname=isnull(oppocompanyname,'')
from fcapdocA_d where rowid=@rowid
exec getxxxx @detailrowid output
--生成子表数据
insert into fCapSubdocA(detailrowid,rowid,cardid,Capcode,Capname,qty,price,orivalue,depvalue,salvalue,netvalue,doccode,flag)
values(@detailrowid,@rowid,@cardid,@Capcode,@Capname,1,@price,@price,@depvalue,@salvalue,isnull(@price,0)-isnull(@depvalue,0),@doccode,
case when @paymethod='估价' then 1 else 0 end)
if @@rowcount=0
begin
close Level1
deallocate Level1
raiserror('更新数据有误！',16,1)
return
end
--生成卡片
insert into facardA(Cardid,oldcode,storplace_id,storplace,companyid,dptid,dptname,capcode,oppocompanyid,oppocompanyname,deptype,years,months,indate,usedate,usestatus,chief,chief_code,examiner,status,CapValue,memo,usetype,rowid,usercode,username
,cardtype)
values(@cardid,@oldcode,@storplace_id,@storplace,@companyid,@dptid,@dptname,@capcode,@oppocompanyid,@oppocompanyname,@deptype,@years,@months,@buydate,@docdate,'使用',@chief,@chiefcode,@examiner,case when @paymethod='估价' then 1 else 0 end,@price,@me
mo,@usetype
,@rowid,@usercode,@username,1)
if @@rowcount=0
begin
close Level1
deallocate Level1
raiserror('更新数据有误！',16,1)
return
end
select @i=@i+1
end
fetch next from Level1 into @companyid,@dptid,@dptname,@capcode,@oldcode,@storplace_id,@storplace,@deptype,@years,@months,@buydate,@docdate,@chief,@chiefcode,@examiner,
@paymethod,@rowid,@Capcode,@Capname,@price,@digit,@depvalue,@salvalue,@allorivalue,@alldepvalue,@memo,@usetype,@usercode,@username
end
close Level1
deallocate Level1
--#1.用不用游标,先测试一下效率再说:
DECLARE @id INT, @doccode INT
DECLARE cursor_Level1 CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY--1.定义成这个类型的游标: STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT id--2.你游标中的变量太多了,如果可以的话,用主键查询方式代替
FROM dbo.VfCapdocA WITH (NOLOCK)
where doccode=@doccode
OPEN cursor_Level1
FETCH NEXT FROM cursor_Level1 INTO @id
WHILE (@@FETCH_STATUS = 0)
BEGIN
--3.用空循环测试一下你游标的效率
/*
insert into fCapSubdocA
SELECT * FROM VfCapdocA WHERE id = @id--2.你游标中的变量太多了,如果可以的话,用主键查询方式代替
*/
FETCH NEXT FROM cursor_Level1 INTO @id
END
CLOSE cursor_Level1
DEALLOCATE cursor_Level1
--#2.循环的效率,不一定比游标好.需要自己测试一下
--#3.当然了,如果不用游标,直接把所有的逻辑用查询实现,一般来说性能最好.
--总结:游标可用;循环不一定就比游标快,还是要了解一下游标和循环之间的性能差异到底在哪儿。对于楼主的需求,个人推荐可用游标(除非测试#1空循环的性能特别差)
