22,207
社区成员
发帖
与我相关
我的任务
分享
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