这个存储过程只能执行一次，第二次就没反应，但也不报错
CREATE PROCEDURE yywgspd_xj @djlsh int AS
declare @ZB_DJLSH int,@QZ varchar(10),@ZHDJH varchar(30),@CJWGSPB_DJLSH int,@djbth int,@gzl_djlsh int
declare @ZDRQ datetime,@GSH varchar(10),@ZDR varchar(10),@ZDID varchar(10),@GL bit,@ysdjh varchar(20)
--select @djlsh = 716
--select @ZDRQ=ZDRQ,@GSH=GSH,@ZDR=ZDR,@ZDID=ZDID,@GL=GL from cjwgdjh where djlsh = @djlsh
declare @erro varchar(500)
if exists (select djlsh from t1573h where ysdjh = @djlsh)
begin
SELECT @ERRO='该单据已提交！'
RAISERROR(@Erro,16,-1)
return
end
if exists (select djlsh from cjwgdjs where djlsh = @djlsh and shtg='N' and isnull(ys,'')='')
begin
SELECT @ERRO='审核通过状态为N的必须输入一审人姓名！'
RAISERROR(@Erro,16,-1)
return
end
declare ys_cursor cursor scroll for
select distinct ys,ysid from cjwgdjs where djlsh = @djlsh and ys is not null and shtg='N'
select @djbth=0
Set NOCOUNT ON;
Set XACT_ABORT ON;
begin tran
open ys_cursor
declare @ys varchar(10),@ysid varchar(10)
declare @dylb varchar(20),@cpmc varchar(60),@dh varchar(60),@gskh varchar(60),@wgsl int,@dysqdh varchar(20),
@ywz varchar(10),@sxh decimal,@hh decimal,@cjcs int,@cjyy varchar(10)
while @@fetch_status=0
begin
if @ys is not null
begin
--表头数据
update djbmh set djh=djh+1 where djdm='CJWGSPB'
select @QZ=djQZ,@zhDJH=DJH-1 from DJbmH where DJdM='CJWGSPB'
select @ZHDJH=Rtrim(@ZHDJH)
select @ZHDJH=ISNULL(@QZ,'')+stuff('00000000',9-len(@ZHDJH),len(@ZHDJH),@ZHDJH)
exec @CJWGSPB_DJLSH=sp_GetDjLsh 'CJWGSPB',@CJWGSPB_DJLSH
insert into t1573h (djlsh,djh,nian,yue,gsh,zdr,zdid,zdrq,gl,ys,ysid,ysdjh)
values (@CJWGSPB_DJLSH,@ZHDJH,Year(@ZDRQ),Month(@ZDRQ),@gsh,@zdr,@zdid,@zdrq,@gl,@ys,@ysid,@djlsh)
--表体数据
declare ysmx_cursor cursor scroll for
select dylb,cpmc,dh,gskh,wgsl,dysqdh,ywz,sxh,hh,cjcs,cjyy from cjwgdjs where djlsh=@djlsh and ysid=@ysid
open ysmx_cursor
--declare @dylb varchar(20),@cpmc varchar(60),@dh varchar(60),@gskh varchar(60),@wgsl int,@dysqdh varchar(20),
-- @ywz varchar(10),@sxh decimal,@hh decimal,@cjcs int,@cjyy varchar(10)
while @@fetch_status=0
begin
if @dh is not null and @gskh is not null
begin
select @djbth=@djbth+1
insert into T1573b(djlsh,djbth,dylb,cpmc,dh,gskh,wgsl,dysqdh,ywz,sxh,ydjh,yhhb,cjcs,cjyy)
values(@CJWGSPB_DJLSH,@djbth,@dylb,@cpmc,@dh,@gskh,@wgsl,@dysqdh,@ywz,@sxh,@ZHDJH,@hh,@cjcs,@cjyy)
end
fetch next from ysmx_cursor into @dylb,@cpmc,@dh,@gskh,@wgsl,@dysqdh,@ywz,@sxh,@hh,@cjcs,@cjyy
end
close ysmx_cursor
deallocate ysmx_cursor
end
fetch next from ys_cursor into @ys,@ysid
end
close ys_cursor
deallocate ys_cursor
if @@error<>0
begin
rollback tran
end
else
commit tran
GO
[解决办法]
CREATE PROCEDURE yywgspd_xj @djlsh INTAS DECLARE @ZB_DJLSH INT , @QZ VARCHAR(10) , @ZHDJH VARCHAR(30) , @CJWGSPB_DJLSH INT , @djbth INT , @gzl_djlsh INT DECLARE @ZDRQ DATETIME , @GSH VARCHAR(10) , @ZDR VARCHAR(10) , @ZDID VARCHAR(10) , @GL BIT , @ysdjh VARCHAR(20)--select @djlsh = 716--select @ZDRQ=ZDRQ,@GSH=GSH,@ZDR=ZDR,@ZDID=ZDID,@GL=GL from cjwgdjh where djlsh = @djlsh DECLARE @erro VARCHAR(500) IF EXISTS ( SELECT djlsh FROM t1573h WHERE ysdjh = @djlsh ) BEGIN SELECT @ERRO = '该单据已提交！' RAISERROR(@Erro,16,-1) RETURN END IF EXISTS ( SELECT djlsh FROM cjwgdjs WHERE djlsh = @djlsh AND shtg = 'N' AND ISNULL(ys, '') = '' ) BEGIN SELECT @ERRO = '审核通过状态为N的必须输入一审人姓名！' RAISERROR(@Erro,16,-1) RETURN END DECLARE ys_cursor CURSOR scroll FOR SELECT DISTINCT ys,ysid FROM cjwgdjs WHERE djlsh = @djlsh AND ys IS NOT NULL AND shtg='N' --是否第二次的时候，此处没有值？ SELECT @djbth = 0 SET NOCOUNT ON ; SET XACT_ABORT ON ; BEGIN TRAN OPEN ys_cursor DECLARE @ys VARCHAR(10) , @ysid VARCHAR(10) DECLARE @dylb VARCHAR(20) , @cpmc VARCHAR(60) , @dh VARCHAR(60) , @gskh VARCHAR(60) , @wgsl INT , @dysqdh VARCHAR(20) , @ywz VARCHAR(10) , @sxh DECIMAL , @hh DECIMAL , @cjcs INT , @cjyy VARCHAR(10) WHILE @@fetch_status = 0 BEGIN IF @ys IS NOT NULL BEGIN--表头数据 UPDATE djbmh SET djh = djh + 1 WHERE djdm = 'CJWGSPB' SELECT @QZ = djQZ , @zhDJH = DJH - 1 FROM DJbmH WHERE DJdM = 'CJWGSPB' SELECT @ZHDJH = RTRIM(@ZHDJH) SELECT @ZHDJH = ISNULL(@QZ, '') + STUFF('00000000', 9 - LEN(@ZHDJH), LEN(@ZHDJH), @ZHDJH) EXEC @CJWGSPB_DJLSH= sp_GetDjLsh 'CJWGSPB', @CJWGSPB_DJLSH INSERT INTO t1573h ( djlsh , djh , nian , yue , gsh , zdr , zdid , zdrq , gl , ys , ysid , ysdjh ) VALUES ( @CJWGSPB_DJLSH , @ZHDJH , YEAR(@ZDRQ) , MONTH(@ZDRQ) , @gsh , @zdr , @zdid , @zdrq , @gl , @ys , @ysid , @djlsh ) --表体数据 DECLARE ysmx_cursor CURSOR scroll FOR SELECT dylb,cpmc,dh,gskh,wgsl,dysqdh,ywz,sxh,hh,cjcs,cjyy FROM cjwgdjs WHERE djlsh=@djlsh AND ysid=@ysid OPEN ysmx_cursor --declare @dylb varchar(20),@cpmc varchar(60),@dh varchar(60),@gskh varchar(60),@wgsl int,@dysqdh varchar(20), -- @ywz varchar(10),@sxh decimal,@hh decimal,@cjcs int,@cjyy varchar(10) WHILE @@fetch_status = 0 BEGIN IF @dh IS NOT NULL AND @gskh IS NOT NULL BEGIN SELECT @djbth = @djbth + 1 INSERT INTO T1573b ( djlsh , djbth , dylb , cpmc , dh , gskh , wgsl , dysqdh , ywz , sxh , ydjh , yhhb , cjcs , cjyy ) VALUES ( @CJWGSPB_DJLSH , @djbth , @dylb , @cpmc , @dh , @gskh , @wgsl , @dysqdh , @ywz , @sxh , @ZHDJH , @hh , @cjcs , @cjyy ) END FETCH NEXT FROM ysmx_cursor INTO @dylb,@cpmc,@dh,@gskh,@wgsl,@dysqdh,@ywz,@sxh,@hh,@cjcs,@cjyy END CLOSE ysmx_cursor DEALLOCATE ysmx_cursor END FETCH NEXT FROM ys_cursor INTO @ys,@ysid END CLOSE ys_cursor DEALLOCATE ys_cursor IF @@error <> 0 BEGIN ROLLBACK TRAN END ELSE COMMIT TRANGO
[解决办法]
这个和存储过程的功能有关系，例如存储过程是删除某参数的数据，删除一次，第二次可能就没有了。
-------------
第二次应该是执行了，但是没有影响到数据。
[解决办法]