create procedure p_CreateCGJHD_CG000001(
@WNBH varchar(20),
@TableName varchar(60)
)
as
declare
@Errs int,
@TZTable varchar(20),
@DDBH VARCHAR(20),
@SQL VARCHAR(MAX),
@SQL2 VARCHAR(8000),
@DJBH VARCHAR(20),
@RQ VARCHAR(10),
@MSG VARCHAR(2000),
@CnT INT,
@ROWCNT INT
Set @RQ=Convert(varchar(10),GetDate(),120)
Set @Cnt=0
SET @TZTable='CGJHD'
Begin Tran
Set @SQL='Declare cur_DJBH cursor for Select Distinct SPDM From '+@TableName+'WHERE XZ=''1'''
Exec(@SQL)
SET @Errs=@ERRS+@@ERROR
Open cur_DJBH
Fetch Next From cur_DJBH INTO @DDBH
While @@FETCH_STATUS=0
Begin
Set @DJBH=IsNull((Select Right(Max(DJBH),6) From CGJHD Where DJBH like 'CG0%' and Len(DJBH)=10),'')
If @DJBH='' Set @DJBH='CG00000001'
Else Set @DJBH='CG'+Left('0000000', 6-Len(Cast(@DJBH as int)+1))+Cast(Cast(@DJBH as int)+1 as varchar)
PRINT @DJBH
Set @SQL='
Insert into '+@TZTable+'(DJBH,RQ,QDDM,DM2,DM2_1,DM1,SL,JE,YS,ZDR,RQ_4,LX)
SELECT '''+@DJBH+''','''+@RQ+''',''000'',''001'',''000'',''000'',SUM(BHSL),0,''0'',''系统生成'','''+@RQ+''',''进货''
FROM '+@TableName+'
Insert into '+@TZTable+'MX(DJBH,SPDM,GG1DM,GG2DM,SL,CKDM,KWDM)
Select '''+@DJBH+''',SPDM,GG1DM,GG2DM,BHSL ,''001'' ,''000''
From '+@TableName+''
PRINT @SQL
Exec(@SQL)
SET @ROWCNT = @@ROWCOUNT
If @@ERROR<>0
Begin
Select '执行失败!' as MSG
Goto AA
End
PRINT @ROWCNT
IF @ROWCNT <> 0
BEGIN
-- Exec(@SQL2)
If @@ERROR<>0
Begin
Select '执行失败!' as MSG
Goto AA
End
Set @Cnt=@Cnt+1
If @Cnt>0 and @Cnt%5=0 Set @MSG=@MSG+','+@DJBH
Else Set @MSG=@MSG+','+@DJBH
END
Fetch Next From cur_DJBH INTO @DDBH
End
Close cur_DJBH
Deallocate cur_DJBH
Commit Tran
RETURN
AA:
Rollback Tran