CREATE TABLE #Table1 (
[HTdate] [datetime] ,
[TimeSetence] [datetime] ,
[IP] [text] COLLATE Japanese_CI_AS ,
[EntryCD] [decimal](18, 0) ,
[UseDate] [datetime] ,
[LotNo] [varchar] (12) COLLATE Japanese_CI_AS ,
[MaterialCD] [varchar] (13) COLLATE Japanese_CI_AS ,
[MakerItemCD] [varchar] (14) COLLATE Japanese_CI_AS ,
[Amount] [money] ,
[MakeDate] [datetime] ,
[IndividualNo] [varchar] (10) COLLATE Japanese_CI_AS
) declare @UseDate datetime
declare @EntryCD decimal
declare @LotNo varchar(12)
declare @MaterialCD varchar(13)
declare @MakerItemCD varchar(15)
declare @Amount money
declare @MakeDate datetime
declare @IndividualNo varchar(12)
declare @ID bigint
declare @useflag bigintdeclare t_FlgNull_Insert cursor scroll forselect
UseDate,
EntryCD,
LotNo,
MaterialCD,
RIGHT(LEFT(Maincd,16),14),
CONVERT(MONEY,RIGHT(LEFT(Maincd, 26),6))/100,
CASE WHEN ISDATE(RIGHT(LEFT(Maincd, 34),6))=0 THEN GETDATE() ELSE CONVERT(DATETIME,RIGHT(LEFT(Maincd, 34),6),120) END,
case when left(SubCD,3)='251' then Substring(SubCD ,4, 10) else case when charindex(' 251',SubCD,1)=0 then '0000000000' else Substring(SubCD,charindex(' 251',SubCD,1)+4,10) end end
from
dbo.T_W_TA_WareHouseOutopen t_FlgNull_Insertfetch next from t_FlgNull_Insert into @UseDate,@EntryCD,@LotNo,@MaterialCD,@MakerItemCD,@Amount,@MakeDate,@IndividualNoWHILE @@FETCH_STATUS = 0
BEGIN select top 1@ID= ID
from dbo.T_D_TA_WareHouseIn
where
MakerItemCD=@MakerItemCD and
Amount=@Amount and
MakeDate=@MakeDate and
IndividualNo=@IndividualNo and
isnull(UseFlag,'')='' and
stateflag<=1000if @ID is nullbegin insert into #table1
(
UseDate,
EntryCD,
LotNo,
MaterialCD,
MakerItemCD,
Amount,
MakeDate,
IndividualNo
)
values
(
@UseDate,
@EntryCD,
@LotNo,
@MaterialCD,
@MakerItemCD,
@Amount,
@MakeDate,
@IndividualNo
)end
else
begin
insert into dbo.T_D_TA_WareHouseOut
(
UseDate,
WareHouseInID,
EntryCD,
LotNo,
MarterialCD,
StateFlag,
StateReason,
UpdateCD,
UpdateDate
)
values
(
@UseDate,
@ID,
@EntryCD,
@LotNo,
@MaterialCD,
0,
'',
297,
getdate()
)
update dbo.T_D_TA_WareHouseIn
set
useflag=1
where
ID=@ID
end
fetch next from t_FlgNull_Insert into @UseDate,@EntryCD,@LotNo,@MaterialCD,@MakerItemCD,@Amount,@MakeDate,@IndividualNoend
close t_FlgNull_Insert
deallocate t_FlgNull_Insert功能:从T_W_TA_WareHouseOut里分解出数据作为条件在T_D_TA_WareHouseIn里找有没有符合条件的ID,要是有把对应的数据插入到T_D_TA_WareHouseOut并将T_D_TA_WareHouseIn的useflag字段更新为1,若不存在则把数据插入到临时表#table1里。现在问题是有一条不符合条件的数据应该ID为空但ID的值却是上一次游标时找到的ID(这是条重复数据),也就是说useflag还是空值,但明明已经更新成1了,我print跟踪过的,我郁闷了,到底是怎么回事
[HTdate] [datetime] ,
[TimeSetence] [datetime] ,
[IP] [text] COLLATE Japanese_CI_AS ,
[EntryCD] [decimal](18, 0) ,
[UseDate] [datetime] ,
[LotNo] [varchar] (12) COLLATE Japanese_CI_AS ,
[MaterialCD] [varchar] (13) COLLATE Japanese_CI_AS ,
[MakerItemCD] [varchar] (14) COLLATE Japanese_CI_AS ,
[Amount] [money] ,
[MakeDate] [datetime] ,
[IndividualNo] [varchar] (10) COLLATE Japanese_CI_AS
) declare @UseDate datetime
declare @EntryCD decimal
declare @LotNo varchar(12)
declare @MaterialCD varchar(13)
declare @MakerItemCD varchar(15)
declare @Amount money
declare @MakeDate datetime
declare @IndividualNo varchar(12)
declare @ID bigint
declare @useflag bigintdeclare t_FlgNull_Insert cursor scroll forselect
UseDate,
EntryCD,
LotNo,
MaterialCD,
RIGHT(LEFT(Maincd,16),14),
CONVERT(MONEY,RIGHT(LEFT(Maincd, 26),6))/100,
CASE WHEN ISDATE(RIGHT(LEFT(Maincd, 34),6))=0 THEN GETDATE() ELSE CONVERT(DATETIME,RIGHT(LEFT(Maincd, 34),6),120) END,
case when left(SubCD,3)='251' then Substring(SubCD ,4, 10) else case when charindex(' 251',SubCD,1)=0 then '0000000000' else Substring(SubCD,charindex(' 251',SubCD,1)+4,10) end end
from
dbo.T_W_TA_WareHouseOutopen t_FlgNull_Insertfetch next from t_FlgNull_Insert into @UseDate,@EntryCD,@LotNo,@MaterialCD,@MakerItemCD,@Amount,@MakeDate,@IndividualNoWHILE @@FETCH_STATUS = 0
BEGIN select top 1@ID= ID
from dbo.T_D_TA_WareHouseIn
where
MakerItemCD=@MakerItemCD and
Amount=@Amount and
MakeDate=@MakeDate and
IndividualNo=@IndividualNo and
isnull(UseFlag,'')='' and
stateflag<=1000if @ID is nullbegin insert into #table1
(
UseDate,
EntryCD,
LotNo,
MaterialCD,
MakerItemCD,
Amount,
MakeDate,
IndividualNo
)
values
(
@UseDate,
@EntryCD,
@LotNo,
@MaterialCD,
@MakerItemCD,
@Amount,
@MakeDate,
@IndividualNo
)end
else
begin
insert into dbo.T_D_TA_WareHouseOut
(
UseDate,
WareHouseInID,
EntryCD,
LotNo,
MarterialCD,
StateFlag,
StateReason,
UpdateCD,
UpdateDate
)
values
(
@UseDate,
@ID,
@EntryCD,
@LotNo,
@MaterialCD,
0,
'',
297,
getdate()
)
update dbo.T_D_TA_WareHouseIn
set
useflag=1
where
ID=@ID
end
fetch next from t_FlgNull_Insert into @UseDate,@EntryCD,@LotNo,@MaterialCD,@MakerItemCD,@Amount,@MakeDate,@IndividualNoend
close t_FlgNull_Insert
deallocate t_FlgNull_Insert功能:从T_W_TA_WareHouseOut里分解出数据作为条件在T_D_TA_WareHouseIn里找有没有符合条件的ID,要是有把对应的数据插入到T_D_TA_WareHouseOut并将T_D_TA_WareHouseIn的useflag字段更新为1,若不存在则把数据插入到临时表#table1里。现在问题是有一条不符合条件的数据应该ID为空但ID的值却是上一次游标时找到的ID(这是条重复数据),也就是说useflag还是空值,但明明已经更新成1了,我print跟踪过的,我郁闷了,到底是怎么回事
declare @UseDate datetime
declare @EntryCD decimal
declare @LotNo varchar(12)
declare @MaterialCD varchar(13)
declare @MakerItemCD varchar(15)
declare @Amount money
declare @MakeDate datetime
declare @IndividualNo varchar(12)
declare @ID bigint
declare @useflag bigintdeclare t_FlgNull_Insert cursor scroll forselect
UseDate,
EntryCD,
LotNo,
MaterialCD,
RIGHT(LEFT(Maincd,16),14),
CONVERT(MONEY,RIGHT(LEFT(Maincd, 26),6))/100,
CASE WHEN ISDATE(RIGHT(LEFT(Maincd, 34),6))=0 THEN GETDATE() ELSE CONVERT(DATETIME,RIGHT(LEFT(Maincd, 34),6),120) END,
case when left(SubCD,3)='251' then Substring(SubCD ,4, 10) else case when charindex(' 251',SubCD,1)=0 then '0000000000' else Substring(SubCD,charindex(' 251',SubCD,1)+4,10) end end
from
dbo.T_W_TA_WareHouseOutopen t_FlgNull_Insertfetch next from t_FlgNull_Insert into @UseDate,@EntryCD,@LotNo,@MaterialCD,@MakerItemCD,@Amount,@MakeDate,@IndividualNoWHILE @@FETCH_STATUS = 0
BEGIN select top 1@ID= ID
from dbo.T_D_TA_WareHouseIn
where
MakerItemCD=@MakerItemCD and
Amount=@Amount and
MakeDate=@MakeDate and
IndividualNo=@IndividualNo and
isnull(UseFlag,'')='' and
stateflag<=1000if @ID is nullbegin insert into #table1
(
UseDate,
EntryCD,
LotNo,
MaterialCD,
MakerItemCD,
Amount,
MakeDate,
IndividualNo
)
values
(
@UseDate,
@EntryCD,
@LotNo,
@MaterialCD,
@MakerItemCD,
@Amount,
@MakeDate,
@IndividualNo
)end
else
begin
insert into dbo.T_D_TA_WareHouseOut
(
UseDate,
WareHouseInID,
EntryCD,
LotNo,
MarterialCD,
StateFlag,
StateReason,
UpdateCD,
UpdateDate
)
values
(
@UseDate,
@ID,
@EntryCD,
@LotNo,
@MaterialCD,
0,
'',
297,
getdate()
)
update dbo.T_D_TA_WareHouseIn
set
useflag=1
where
ID=@ID
end
set @ID = NULL --赋值为NULL
fetch next from t_FlgNull_Insert into @UseDate,@EntryCD,@LotNo,@MaterialCD,@MakerItemCD,@Amount,@MakeDate,@IndividualNoend
close t_FlgNull_Insert
deallocate t_FlgNull_Insert