求助sybase逐行循环问题，加急

[请叫我-雷人]

ALTER PROCEDURE "DBA"."ee"(@vsyjh char(4),@ret integer output)

as
begin
declare @FPHM numeric(20)
declare @SHBC char
declare @LSHBC char(4)
declare @SHSYJH char(4)
declare @SHFPHM integer
declare @SHDJLB char
declare @SHDATE datetime
declare @SHSYYH char(15)
declare @SHHYKH char(13)
declare @SHJFKH char(13)
declare @SHTHSQ char(13)
declare @SHGHSQ char(13)
declare @SHHYSQ char(13)
declare @SHSQKH char(15)
declare @SHSQKTYPE char
declare @SHSQKZKFD numeric(15,2)
declare @SHYSJE numeric(15,2)
declare @SHSJFK numeric(15,2)
declare @SHZL numeric(15,2)
declare @SHSYSY numeric(15,2)
declare @SHHYZKE numeric(15,2)
declare @SHYHZKE numeric(15,2)
declare @SHLSZKE numeric(15,2)
declare @SHLSZRE numeric(15,2)
declare @SHCUSTINFO char(6)
declare @SHYJJF numeric(15,2)
declare @SHNUM1 numeric(12,6)
declare @SHNUM2 numeric(12,6)
declare @SHNUM3 numeric(12,6)
declare @SHSTR1 char(20)
declare @SHSTR2 char(20)
declare @SHSTR3 char(20)
declare @RSEQ numeric(20)
declare @SDROWNO integer
declare @SDYYYH char(15)
declare @SDBARCODE char(13)
declare @SDCODE char(13)
declare @SDTYPE char
declare @SDGZ char(20)
declare @SDDZXL char(10)
declare @SDPP char(6)
declare @SDSPEC char(2)
declare @SDBATCH char(15)
declare @SDPOPBNO char(15)
declare @SDNAME char(40)
declare @SDUNIT char(4)
declare @SDBZHL numeric(15,4)
declare @SDSL numeric(15,4)
declare @SDLSJ numeric(15,2)
declare @SDJG numeric(15,2)
declare @SDZJE numeric(15,2)
declare @SDHYZKE numeric(15,2)
declare @SDYHZKE numeric(15,2)
declare @SDYHZKFD numeric(12,6)
declare @SDLSZKE numeric(15,2)
declare @SDLSZRE numeric(15,2)
declare @SDZZKE numeric(15,2)
declare @SDZZRE numeric(15,2)
declare @SDPLZKE numeric(15,2)
declare @SDZSZKE numeric(15,2)
declare @SDSQKH char(13)
declare @SDSQKTYPE char
declare @SDPFZKFD numeric(12,6)
declare @SDSPZKFD numeric(12,6)
declare @SDSQKZKFD numeric(12,6)
declare @SDISVIPZK char
declare @SDXXTAX numeric(12,6)
declare @SDFLAG char
declare @SDYJHXCODE char(13)
declare @SDYSYJH char(4)
declare @SDYFPHM integer
declare @SDYJJF numeric(15,2)
declare @SDNUM1 numeric(12,6)
declare @SDNUM2 numeric(12,6)
declare @SDNUM3 numeric(12,6)
declare @SDSTR1 char(20)
declare @SDSTR2 char(20)
declare @SDSTR3 char(20)
declare @SPPMCODE char(4)
declare @SPMONEY numeric(15,2)
declare @SPERATE numeric(12,6)
declare @SPNO char(20)
declare @SPPAYERID char(20)
declare @SPYE numeric(15,2)
declare @PMPCODE char(2)
declare @lsign integer --YOLU上传的数据符号
declare @RMB numeric(15,2)
declare @USB numeric(15,2)
declare @USBHL numeric(12,6)
declare @HKD numeric(15,2)
declare @HKDHL numeric(12,6)
declare @XJZP numeric(15,2)
declare @ZZJZP numeric(15,2)
declare @XYK numeric(15,2)
declare @LQ numeric(15,2)
declare @ICK numeric(15,2)
declare @MZK numeric(15,2)
declare @SZ numeric(15,2)
declare @PAY11 numeric(15,2)
declare @HL11 numeric(12,6)
declare @PAY12 numeric(15,2)
declare @HL12 numeric(12,6)
declare @PAY13 numeric(15,2)
declare @HL13 numeric(12,6)
declare @PAY14 numeric(15,2)
declare @HL14 numeric(12,6)
declare @PAY15 numeric(15,2)
declare @HL15 numeric(12,6)

declare cur_head1 dynamic scroll cursor for select SHFPHM from
sellhead where SHSYJH = @vsyjh and SHNETBZ = 'N' --and SPFPHM=@vfphm
open cur_head1

fetch cur_head1 into @FPHM

while @@sqlstatus != 2
begin

declare cur_com dynamic scroll cursor for select SDROWNO,SDYYYH,SDBARCODE,SDCODE,SDTYPE,SDGZ,SDDZXL,SDPP,SDSPEC,SDBATCH,SDPOPBNO,SDNAME,
SDUNIT,SDBZHL,SDSL,SDLSJ,SDJG,SDZJE,SDHYZKE,SDYHZKE,SDYHZKFD,SDLSZKE,SDLSZRE,SDZZKE,
SDZZRE,SDPLZKE,SDZSZKE,SDSQKH,SDSQKTYPE,SDPFZKFD,SDSPZKFD,SDSQKZKFD,SDISVIPZK,SDXXTAX,
SDFLAG,SDYJHXCODE,SDYSYJH,SDYFPHM,SDYJJF,SDNUM1,SDNUM2,SDNUM3,SDSTR1,SDSTR2,SDSTR3 from
selldetail where SDSYJID = @vsyjh and SDFPHM=@FPHM order by
SDROWNO asc

declare @loop integer
declare @lSSTAPPVER char
select @lSSTAPPVER = SSTAPPVER from SYJSETUP
if @@error <> 0 goto procfail

select @SHBC = SHBC,@SHSYJH = SHSYJH,@SHFPHM = SHFPHM,@SHDJLB = SHDJLB,@SHDATE = SHDATE,@SHSYYH = SHSYYH,@SHHYKH = SHHYKH,@SHJFKH = SHJFKH,@SHTHSQ = SHTHSQ,@SHGHSQ = SHGHSQ,@SHHYSQ = SHHYSQ,@SHSQKH = SHSQKH,@SHSQKTYPE = SHSQKTYPE,@SHSQKZKFD = SHSQKZKFD,
@SHYSJE = SHYSJE,@SHSJFK = SHSJFK,@SHZL = SHZL,@SHSYSY = SHSYSY,@SHHYZKE = SHHYZKE,@SHYHZKE = SHYHZKE,@SHLSZKE = SHLSZKE,@SHLSZRE = SHLSZRE,@SHCUSTINFO = SHCUSTINFO,@SHYJJF = SHYJJF,@SHNUM1 = SHNUM1,@SHNUM2 = SHNUM2,@SHNUM3 = SHNUM3,@SHSTR1 = SHSTR1,@SHSTR2 = SHSTR2,@SHSTR3 = SHSTR3 from
sellhead where SHSYJH = @vsyjh and SHFPHM = @FPHM

if @@error <> 0 goto procfail


if @lSSTAPPVER = '1'
else --yolu
begin
--获得符号
execute SP_SENDSALEHEAD_YOLU @SHSYJH,@SHFPHM,convert(char(19),@SHDATE,120),@SHDJLB,@SHYSJE*@lsign,@SHSJFK*@lsign,@SHZL*@lsign,@SHLSZKE*@lsign,@SHLSZRE*@lsign,@SHHYZKE*@lsign,@SHYHZKE*@lsign,@SHSYSY*@lsign,@SHSYYH,@LSHBC,@SHHYKH,@RMB*@lsign,@USB*@lsign,@USBHL,@HKD*@lsign,@HKDHL,@XJZP*@lsign,@ZZJZP*@lsign,@XYK*@lsign,@LQ*@lsign,@ICK*@lsign,@MZK*@lsign,@SZ*@lsign,@PAY11*@lsign,@HL11,@PAY12*@lsign,@HL12,@PAY13*@lsign,@HL13,@PAY14*@lsign,@HL14,@PAY15*@lsign,@HL15,@SHYJJF*@lsign,@SHNUM1,@SHNUM2,@SHNUM3,@SHSTR1,@SHSTR2,@SHSTR3,@ret,@RSEQ
if sqlcode = -823 or sqlcode = -660 or sqlcode = -656 goto lostnet
if @ret = 2
begin
select @ret=1
goto procsucceed
end
open cur_com
if @@error <> 0 goto procfail
select @loop=1
while @loop = 1
select 2
begin
fetch next cur_com into @SDROWNO,@SDYYYH,@SDBARCODE,@SDCODE,@SDTYPE,@SDGZ,@SDDZXL,@SDPP,@SDSPEC,@SDBATCH,@SDPOPBNO,@SDNAME,
@SDUNIT,@SDBZHL,@SDSL,@SDLSJ,@SDJG,@SDZJE,@SDHYZKE,@SDYHZKE,@SDYHZKFD,@SDLSZKE,@SDLSZRE,@SDZZKE,
@SDZZRE,@SDPLZKE,@SDZSZKE,@SDSQKH,@SDSQKTYPE,@SDPFZKFD,@SDSPZKFD,@SDSQKZKFD,@SDISVIPZK,@SDXXTAX,
@SDFLAG,@SDYJHXCODE,@SDYSYJH,@SDYFPHM,@SDYJJF,@SDNUM1,@SDNUM2,@SDNUM3,@SDSTR1,@SDSTR2,
@SDSTR3
if @@fetch_status <> 0
select @loop=0
else
begin
if(LENGTH(@SDBARCODE) = 13 or LENGTH(@SDBARCODE) = 12) and SUBSTRING(@SDBARCODE,1,1) = '2' select @SDBARCODE=@SDCODE
if(LENGTH(@SDBARCODE) in( 12,13,18) ) and LOCATE(@SDBARCODE,@SDCODE,1) > 0 select @SDBARCODE=@SDCODE
if @SDBARCODE = @SDCODE select @SDTYPE='1'
else select @SDTYPE='4'
execute SP_SENDSALECOM_YOLU @RSEQ,@SHSYJH,@SHFPHM,convert(char(19),@SHDATE,120),@SDYYYH,@SDBARCODE,
@SDTYPE,@SDGZ,@SDSL*@lsign,@SDLSJ,@SDJG,(@SDLSZKE+@SDZZKE)*@lsign,(@SDLSZRE+@SDZZRE)*@lsign,@SDHYZKE*@lsign,@SDYHZKE*@lsign,
@SDYHZKFD,@SDYSYJH,@SDYFPHM,@SDDZXL,@SDPP,@SDPOPBNO,@SDYJHXCODE,@SDSPZKFD,@SDYJJF*@lsign,
@SDBZHL,@SDNUM2,@SDNUM3,@SDCODE,@SDSTR2,@SDSTR3,@RET
if sqlcode = -823 or sqlcode = -660 or sqlcode = -656 goto lostnet
if @RET = 0 goto procfail
end
end
close cur_com
execute SP_COMMIT_YOLU
if sqlcode = -823 or sqlcode = -660 or sqlcode = -656 goto lostnet
end

fetch next cur_head1 into @FPHM

end
close cur_head1
goto procsucceed
procfail: select @ret=0
return-1
lostnet: select @ret=-1
return-1
procsucceed: select @ret=1
return 1
end


这个过程有什么问题？为什么只能取到cur_head1中的一行信息？？？？？？？？？

[WWWWA]

你用了嵌套游标，检查每个游标循环情况，

declare cur_com dynamic scroll cursor for select SDROWNO,SDYYYH,SDBARCODE,SDCODE,SDTYPE,SDGZ,SDDZXL,SDPP,SDSPEC,SDBATCH,SDPOPBNO,SDNAME,
SDUNIT,SDBZHL,SDSL,SDLSJ,SDJG,SDZJE,SDHYZKE,SDYHZKE,SDYHZKFD,SDLSZKE,SDLSZRE,SDZZKE,
SDZZRE,SDPLZKE,SDZSZKE,SDSQKH,SDSQKTYPE,SDPFZKFD,SDSPZKFD,SDSQKZKFD,SDISVIPZK,SDXXTAX,
SDFLAG,SDYJHXCODE,SDYSYJH,SDYFPHM,SDYJJF,SDNUM1,SDNUM2,SDNUM3,SDSTR1,SDSTR2,SDSTR3 from
selldetail where SDSYJID = @vsyjh and SDFPHM=@FPHM order by
SDROWNO asc
定义在第1个循环之外

参考示例：
CREATE PROCEDURE dbo.h11_ylg_temp;1

AS

BEGIN
/*创建临时表*/
Create Table #temp_hz
(zybh char(10),brxm char(10) null,fylbid char(10) null,
xmje decimal(4,2) null,zxrq char(10) null)
INSERT #temp_hz(zybh,brxm,fylbid,xmje,zxrq)

--按费用类别统计
SELECT zybh,brxm,fylbid,Isnull(sum(xmje),0),zxrq
from ylg_temp
GROUP BY zybh,brxm,fylbid,zxrq,fylbid

DECLARE @ls_zybh char(10),@ls_brxm char (10),@ls_fylbid char(10),
@ldc_xmje decimal(4,2),@ls_zxrq char(10)
--定义游标1
DECLARE CR_BRXX CURSOR FOR
SELECT zybh,brxm,zxrq FROM #temp_hz
--定义游标2
DECLARE CR_FYLBID CURSOR FOR
SELECT fylbid FROM h00_fylb
--打开游标1
OPEN CR_BRXX
--取值1
FETCH CR_BRXX INTO @ls_zybh,@ls_brxm,@ls_zxrq
WHILE @@SQLSTATUS=0
BEGIN
--打开游标2
OPEN CR_FYLBID
--取值2
FETCH CR_FYLBID INTO @ls_fylbid
WHILE @@SQLSTATUS=0
BEGIN
IF NOT EXISTS(SELECT * FROM #temp_hz WHERE
zybh=@ls_zybh and fylbid=@ls_fylbid)
/*没有费用类别的加一行*/
INSERT INTO #temp_hz (zybh,brxm,fylbid,xmje,zxrq)
VALUES (@ls_zybh,@ls_brxm,@ls_fylbid,0,@ls_zxrq)
FETCH CR_FYLBID INTO @ls_fylbid
END
--关闭游标2
CLOSE CR_FYLBID

FETCH CR_BRXX INTO @ls_zybh,@ls_brxm,@ls_zxrq
END
DEALLOCATE CURSOR CR_FYLBID
--关闭游标1
CLOSE CR_BRXX
DEALLOCATE CURSOR CR_BRXX
--生成表
SELECT * FROM #temp_hz
Drop Table #temp_hz
END

[请叫我-雷人]

谢谢。已经解决。我新写了个的过程定义单个游标然后在调用另一个过程。解决了。