我快发疯了，我的意思是，这么多天来我都搞不清楚发生了什么。
客户机正在使用它并将数据插入到表中。
eq\u header是一个表，在将数据放入其中之后，我们取它的主键eq\u headerid并放入其他表中。
但问题是
在某些情况下。。。。我们从父表得到的主键在所有其他子表中都是重复的，即使equipmentno是不同的。只有1比1，但为什么在很多情况下，headerid会重复出现呢？
ALTER PROCEDURE [dbo].[InsertEquipmentsDATA]
@FunctionalLocation varchar(30)= null ,
@EquipmentNo varchar(30) = null ,
@IsGeneralEmpty bit=0,
@ANSDTSpecified bit= null,
@ANSDT_General date= null,
@ANSWT_General varchar(13)= null,
@BAUJJ_General varchar(4)= null,
@BAUMM_General varchar(2)= null,
@BEGRU_General varchar(4)= null,
@EQART_General varchar(10)= null,
@ERDAT_General date= null,
@ERDATSpecified bit= null,
@HERLD_General varchar(3)= null,
@HZEIN_General varchar(30)= null,
@HERST_General varchar(30)= null,
@INBDT_General date= null,
@INBDTSpecified bit= null,
@SERGE_General varchar(30)= null,
@TYPBZ_General varchar(20)= null,
@IsHeaderEmpty bit=0,
@DATAB_Header date= null,
@DATABSpecified bit= null,
@DATABI_Header date= null,
@DATABISpecified bit= null,
@EQTYP varchar(1)= null,
@EQUNR varchar(18)= null,
@GUID_Header varchar(36)= null,
@SHTXT_Header varchar(40)= null,
@STTXT_Header varchar(4)= null,
@IsLocationEmpty bit=0,
@ABCKZ_Location varchar(1)= null,
@BEBER_Location varchar(3)= null,
@EQFNR_Location varchar(30)= null,
@MSGRP_Location varchar(8)= null,
@STORT_Location varchar(10)= null,
@SWERK_Location varchar(4)= null,
@IsOrganizationEmpty bit= 0,
@ANLNR_Organizations varchar(12)= null,
@BUKRS_Organizations varchar(4)= null,
@GEWRK_Organizations varchar(20)= null,
@GSBER_Organizations varchar(4)= null,
@INGRP_Organizations varchar(3)= null,
@IWERK_Organizations varchar(4)= null,
@KOKRS_Organizations varchar(4)= null,
@KOSTL_Organizations varchar(10)= null,
@PROID_Organizations varchar(20)= null,
@RBNR_Organizations varchar(9)= null,
--@ATINN_Classification int,
--@ATWRT_Classification varchar(70),
@IsStructureEmpty bit=0,
@HEQNR_Structure varchar(4)= null,
@HEQUI_Structure varchar(18)= null,
@POSNR_Structure varchar(4)= null,
@SUBMT_Structure varchar(40)= null,
@TIDNR_Structure varchar(25)= null,
@TPLNR_Structure varchar(40)= null,
@ATNAM_Characteristic varchar(50)= null,
@ATZHL_Characteristic varchar(3)= null,
@ATBEZ_Characteristic varchar(40)= null,
@ATWRT_Value varchar(40)= null,
@EINHE_Value varchar(6)= null ,
@ATZHL_Value varchar(3)= null ,
@IsSerialEmpty bit= 0,
@B_CHARGE varchar(10)= null,
@B_LAGER varchar(4)= null,
@B_WERK varchar(4)= null ,
@CHARGE varchar(10) = null ,
@LBBSA varchar(2) = null ,
@MATNR varchar(40) = null ,
@SERNR varchar(18) = null ,
@IsWarrantyEmpty bit= 0,
@GWLDT date= null ,
@GWLDTSpecified bit= null,
@GWLEN date = null ,
@GWLENSpecified bit= null,
@ClassificationCollection Classification readonly,
@CharacteristicCollection Characteristic readonly,
@ValueCollection Value readonly,
@StatusRet varchar(20) out,
@ErrorRet varchar(1000) out
AS
BEGIN
Begin Try
Begin Transaction
Declare @Status Char(1)
SET @Status='N'
IF exists (Select * from EQ_Header where EQUNR= @EQUNR)
Begin
Set @StatusRet= 'Change'
Set @ErrorRet= ''
SET @Status='U'
End
-- --Update EQ_Header
-- --Set
-- -- EquipmentNo = @EquipmentNo ,
-- -- DATAB = @DATAB_Header,
-- -- DATABSpecified = @DATABSpecified ,
-- -- DATBI = @DATABI_Header ,
-- -- DATBISpecified = @DATABISpecified ,
-- -- EQTYP = @EQTYP ,
-- -- EQUNR = @EQUNR ,
-- -- SHTXT = @SHTXT_Header ,
-- -- STTXT = @STTXT_Header
-- --where EQUNR= @EQUNR
-- Set @STATUSRet= 'Change'
-- Set @ErrorRet= (Select ERROR_MESSAGE())
-- Commit
-- return
--End
Insert into FunctionalLocations
(
FunctionalLocation
)
values
(
@FunctionalLocation
)
Insert into [dbo].[Equipment]
(
EquipmentNo,
FunctionalLocation,
Status
)
values
(
@EQUNR,
@FunctionalLocation,
'Pending'
)
IF(@IsHeaderEmpty = 0)
Begin
Insert into [dbo].[EQ_Header]
(
EquipmentNo ,
DATAB ,
DATABSpecified ,
DATBI ,
DATBISpecified ,
EQTYP ,
EQUNR ,
SHTXT ,
STTXT,
GUID,
Status,
Transferred
)
values
(
@EquipmentNo ,
@DATAB_Header ,
@DATABSpecified ,
@DATABI_Header ,
@DATABISpecified ,
@EQTYP ,
@EQUNR ,
@SHTXT_Header,
@STTXT_Header,
@GUID_Header,
@Status,
0
)
End
Declare @HeaderID int
Set @HeaderID= IDENT_CURRENT('EQ_Header') --Latest Header ID
IF(@IsGeneralEmpty = 0)
Begin
Insert into [dbo].[EQ_General]
(
EquipmentNo ,
ANSDT ,
ANSDTSpecified ,
ANSWT ,
BAUJJ ,
BAUMM ,
BEGRU ,
EQART ,
ERDAT ,
ERDATSpecified ,
HERLD ,
HERST ,
HZEIN ,
INBDT ,
INBDTSpecified ,
SERGE ,
TYPBZ ,
HeaderID
)
values
(
@EquipmentNo ,
@ANSDT_General ,
@ANSDTSpecified ,
@ANSWT_General ,
@BAUJJ_General ,
@BAUMM_General ,
@BEGRU_General ,
@EQART_General ,
@ERDAT_General ,
@ERDATSpecified ,
@HERLD_General ,
@HERST_General ,
@HZEIN_General,
@INBDT_General ,
@INBDTSpecified ,
@SERGE_General ,
@TYPBZ_General ,
@HeaderID
)
End
IF(@IsLocationEmpty = 0)
Begin
Insert into [dbo].[EQ_Location]
(
EquipmentNo,
ABCKZ ,
BEBER ,
EQFNR ,
MSGRP ,
STORT ,
SWERK,
HeaderID
)
values
(
@EquipmentNo,
@ABCKZ_Location ,
@BEBER_Location ,
@EQFNR_Location ,
@MSGRP_Location ,
@STORT_Location ,
@SWERK_Location,
@HeaderID
)
End
IF(@IsOrganizationEmpty = 0)
Begin
Insert into [dbo].[EQ_Organizations]
(
EquipmentNo,
ANLNR ,
BUKRS ,
GEWRK ,
GSBER ,
INGRP ,
IWERK ,
KOKRS ,
KOSTL ,
PROID ,
RBNR ,
HeaderID
)
values
(
@EquipmentNo,
@ANLNR_Organizations ,
@BUKRS_Organizations ,
@GEWRK_Organizations ,
@GSBER_Organizations ,
@INGRP_Organizations ,
@IWERK_Organizations ,
@KOKRS_Organizations ,
@KOSTL_Organizations ,
@PROID_Organizations ,
@RBNR_Organizations ,
@HeaderID
)
End
IF(@IsStructureEmpty = 0)
Begin
Insert into [dbo].[EQ_Structure]
(
EquipmentNo,
HEQNR ,
HEQUI ,
POSNR ,
SUBMT ,
TIDNR ,
TPLNR,
HeaderID
)
values
(
@EquipmentNo,
@HEQNR_Structure ,
@HEQUI_Structure ,
@POSNR_Structure ,
@SUBMT_Structure ,
@TIDNR_Structure ,
@TPLNR_Structure,
@HeaderID
)
End
IF(@IsSerialEmpty = 0)
Begin
Insert into [dbo].[EQ_Serial]
(
B_CHARGE,
B_LAGER ,
B_WERK ,
CHARGE ,
LBBSA ,
MATNR ,
SERNR,
HeaderID
)
values
(
@B_CHARGE,
@B_LAGER ,
@B_WERK ,
@CHARGE ,
@LBBSA ,
@MATNR ,
@SERNR,
@HeaderID
)
End
IF(@IsWarrantyEmpty = 0)
Begin
Insert into [dbo].[EQ_Warranty]
(
EquipmentNo ,
GWLDT ,
GWLDTSpecified ,
GWLEN ,
GWLENSpecified,
HeaderID
)
values
(
@EquipmentNo ,
@GWLDT ,
@GWLDTSpecified ,
@GWLEN ,
@GWLENSpecified,
@HeaderID
)
End
Declare @ClassificationTypeCount int
Set @ClassificationTypeCount= (Select Count(*) from @ClassificationCollection)
If(@ClassificationTypeCount > 0)
Begin
--Classification
Insert into [dbo].Eq_Classification
(
EquipmentNo,
CLASS,
KLTXT,
KLART,
HeaderID
)
--values
--(
-- @EquipmentNo,
-- @CLASS_Classification,
-- @KLTXT_Classification,
-- @KLART_Classification
--)
Select @EquipmentNo, CLASS, KLTXT, KLART, @HeaderID from @ClassificationCollection
Declare @ClassificationID int
Set @ClassificationID= IDENT_CURRENT('Eq_Classification')
END
--Characteristic
Declare @CharacteristicTypeCount int
Set @CharacteristicTypeCount= (Select Count(*) from @CharacteristicCollection)
If(@CharacteristicTypeCount > 0)
Begin
Insert into [dbo].EQ_Characteristic
(
EquipmentNo,
ATNAM,
ATZHL,
ATBEZ,
HeaderID,
ClassificationID
)
--values
--(
-- @ATNAM_Characteristic,
-- @ATZHL_Characteristic,
-- @ATBEZ_Characteristic
--)
Select @EquipmentNo , ATNAM, ATZHL, ATBEZ, @HeaderID, @ClassificationID from @CharacteristicCollection
END
--Value
Declare @ValueTypeCount int
Set @ValueTypeCount= (Select Count(*) from @ValueCollection)
If(@ValueTypeCount > 0)
Begin
Insert into [dbo].EQ_Value
(
EquipmentNo,
ATWRT,
EINHE,
ATZHL,
HeaderID,
ClassificationID
)
--values
--(
-- @EquipmentNo,
-- @ATWRT_Value,
-- @EINHE_Value,
-- @ATZHL_Value
--)
Select @EquipmentNo, ATWRT, EINHE, ATZHL, @HeaderID, @ClassificationID from @ValueCollection
End
IF(ISNULL(@StatusRet , '') = '')
Begin
Set @STATUSRet= 'Created'
Set @ErrorRet= ''
End
Commit Transaction
End Try
Begin Catch
Set @STATUSRet= 'Failed'
Set @ErrorRet= ( Select CAST(Error_Line() as Varchar(20)) + ':' +ERROR_MESSAGE())
Rollback Transaction
End Catch
END
2条答案
按热度按时间68de4m5k1#
我建议使用
SCOPE_IDENTITY而不是IDENT_CURRENT.SCOPE_IDENTITY将为您提供为当前会话和当前作用域插入的最新标识值。鉴于IDENT_CURRENT将为您提供表中所有会话的值。如果有多个客户机同时调用此过程，则可能从其他客户机获取标识值。o7jaxewo2#
恐怕您的存储过程太长，无法像这样完全解析，但我怀疑如果此问题是间歇性的，那么您可能会发现错误与以下行有关：
你应该读一下：
https://docs.microsoft.com/en-us/sql/t-sql/functions/ident-current-transact-sql?view=sql-server-ver15
特别是本节：
ident\u current类似于sql server 2000（8.x）identity函数scope\u identity和@identity。这三个函数都返回最后生成的标识值。但是，每个函数中定义last的范围和会话不同：
ident\u current返回在任何会话和任何作用域中为特定表生成的最后一个标识值。
@@identity返回为当前会话中所有作用域中的任何表生成的上一个identity值。
scope\u identity返回为当前会话和当前作用域中的任何表生成的最后一个标识值。
您当前正在为任何作用域和任何会话获取该表的最后生成的id。您可能应该将其更改为scope\u identity。