第一次用游标来复制一个较为复杂的单据

第一次用游标来复制一个较为复杂的单据(和大家一起分享一下):

-- =============================================
-- Create procedure basic template --复制工艺成本单
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'CopyGongYiChengBenDan'
AND type = 'P')
DROP PROCEDURE CopyGongYiChengBenDan
GO

CREATE PROCEDURE CopyGongYiChengBenDan
@SourceID varchar(50),
@DestID varchar(50),
@VersionID int
AS

--申明主表字段(GongYCBD)
Declare @HSHM1 VarChar(20)
Declare @HSHM2 VarChar(20)
Declare @HSHM3 VarChar(20)
Declare @QDL float
Declare @ZhiDR Varchar(20)
Declare @DNJGS float
Declare @DNJGJ float
Declare @GZGS float
Declare @GZGJ float
Declare @TKGS float
Declare @TKGJ float
Declare @HJGS float
Declare @HJGJ float
Declare @XHZJ float
Declare @YUZJ float
Declare @FLZJ float
Declare @HJ float
Declare @PinM Varchar(255)
Declare @EPingM Varchar(255)
Declare @ZhiGR Varchar(20)
Declare @Picture1 Binary
Declare @Picture2 Binary
Declare @Picture3 Binary
Declare @Picture4 Binary
Declare @Picture5 Binary
Declare @Iyear Varchar(4)
Declare @Imonth Varchar(2)
--工艺成本单明细(GongYCBDMX)
--,,,,GYCBDBH,,
Declare @YuanLMC Varchar(50)
Declare @GYSBM Varchar(20)
Declare @ShuL float
Declare @SHL numeric(5,2)
Declare @JLDW Varchar(10)
Declare @YBDJ float
Declare @BBDJ float
Declare @YBJE float
Declare @BBJE float
Declare @BB Varchar(10)
Declare @YB Varchar(10)
Declare @ISYuanLiao bit
DECLARE GongYCBDMX_cursor CURSOR
FOR SELECT YuanLMC,GYSBM,ShuL,SHL,JLDW,YBDJ,BBDJ,YBJE,BBJE,BB,YB,ISYuanLiao FROM GongYCBDMX where GYCBDBH=@SourceID
--Declare @BBH float
--工艺成本单成份关系表
--GongYCBDBH,ChengFBM,ChengFBL,ShiFYY
Declare @ChengFBM bigint
Declare @ChengFBL numeric(5,2)
DECLARE ChengF_cursor CURSOR
FOR SELECT ChengFBM,ChengFBL FROM 工艺成本单成份关系表 where GongYCBDBH=@SourceID and ShiFYY=0
----工艺成本单样衣分类关系表
--FengLBM,GongYCBDBH,ShiFYY
Declare @FengLBM Varchar(20)
DECLARE FengL_cursor CURSOR
FOR SELECT FengLBM FROM 工艺成本单样衣分类关系表 where GongYCBDBH=@SourceID and ShiFYY=0
--复制主表
Select @HSHM1=HSHM1,@HSHM2=HSHM2,@HSHM3=HSHM3,@QDL=QDL,@ZhiDR=ZhiDR,@DNJGS=DNJGS,@DNJGJ=DNJGJ,@GZGS=GZGS,@GZGJ=GZGJ,@TKGS=TKGS,@TKGJ=TKGJ,@HJGS=HJGS,@HJGJ=HJGJ,@XHZJ=XHZJ,@YUZJ=YUZJ,@FLZJ=FLZJ,@HJ=HJ,@PinM=PinM,@EPingM=EPingM,@ZhiGR=ZhiGR,@Picture1=Picture1,@Picture2=Picture2,@Picture3=Picture3,@Picture4=Picture4,@Picture5=Picture5, @Iyear=Iyear,@Imonth=Imonth
from GongYCBD where ID=@SourceID
update GongYCBD set HSHM1=@HSHM1,HSHM2=@HSHM2,HSHM3=@HSHM3,QDL=@QDL,ZhiDR=@ZhiDR,DNJGS=@DNJGS,DNJGJ=@DNJGJ,GZGS=@GZGS,GZGJ=@GZGJ,TKGS=@TKGS,TKGJ=@TKGJ,HJGS=@HJGS,HJGJ=@HJGJ,XHZJ=@XHZJ,YUZJ=@YUZJ,FLZJ=@FLZJ,HJ=@HJ,PinM=@PinM,EPingM=@EPingM,ZhiGR=@ZhiGR,Picture1=@Picture1,Picture2=@Picture2,Picture3=@Picture3,Picture4=@Picture4,Picture5=@Picture5, Iyear=@Iyear,Imonth=@Imonth
where ID=@DestID
--复制工艺成本单明细
Open GongYCBDMX_cursor
FETCH NEXT FROM GongYCBDMX_cursor into @YuanLMC,@GYSBM,@ShuL,@SHL,@JLDW,@YBDJ,@BBDJ,@YBJE,@BBJE,@BB,@YB,@ISYuanLiao
WHILE @@FETCH_STATUS = 0
BEGIN
Insert into GongYCBDMX(GYCBDBH,BBH,YuanLMC,GYSBM,ShuL,SHL,JLDW,YBDJ,BBDJ,YBJE,BBJE,BB,YB,ISYuanLiao)Values(@DestID,@VersionID,@YuanLMC,@GYSBM,@ShuL,@SHL,@JLDW,@YBDJ,@BBDJ,@YBJE,@BBJE,@BB,@YB,@ISYuanLiao)
FETCH NEXT FROM GongYCBDMX_cursor into @YuanLMC,@GYSBM,@ShuL,@SHL,@JLDW,@YBDJ,@BBDJ,@YBJE,@BBJE,@BB,@YB,@ISYuanLiao
END
CLOSE GongYCBDMX_cursor
DEALLOCATE GongYCBDMX_cursor

--复制成份关系表
Open ChengF_cursor
FETCH Next from ChengF_cursor into @ChengFBM,@ChengFBL
While @@FETCH_STATUS = 0
Begin
Insert into 工艺成本单成份关系表(GongYCBDBH,ChengFBM,ChengFBL)Values(@DestID,@ChengFBM,@ChengFBL)
FETCH Next from ChengF_cursor into @ChengFBM,@ChengFBL
END
Close ChengF_cursor
DEAllocate ChengF_cursor
--复制分类关系表
Open FengL_cursor
FETCH Next from FengL_cursor into @FengLBM
While @@FETCH_STATUS =0
Begin
Insert into 工艺成本单样衣分类关系表(GongYCBDBH,FengLBM)Values(@DestID,@FengLBM)
FETCH Next from FengL_cursor into @FengLBM
end
close FengL_cursor
DeAllocate FengL_cursor
GO

-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE CopyGongYiChengBenDan '1','2',1
GO

--delete from dbo.GongYCBDMX where GYCBDBH<>1

--delete from dbo.工艺成本单成份关系表 where GongYCBDBH<>1

--delete from dbo.工艺成本单样衣分类关系表 where GongYCBDBH<>1

请大家多多指教