大侠帮忙看看这个存储过程为啥执行两次,导致主键原因不能插入。小弟存储过程初学,希望大家帮帮忙,在线等
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER Procedure [dbo].[Proc_OP2100_WriteFinish]
(
@EngineStreamNO Nvarchar(50)
)
as declare --@strEngineStreamNO Nvarchar(50),
@intErrorCode INT,
@intRowCount INT,
@SQL nvarchar(4000)set @SQL=''if not Exists (select 1 from ShortHistoryOnLineOrder where EngineStreamNO=@EngineStreamNO )
begin
--增加下线订单记录
set @SQL ='insert into ShortHistoryOnLineOrder(EngineStreamNO,OnLine_OrderNO,OnLine_CastNO,OnLine_PalletNO,
EngineTypeCode,BomName,InstructionName,OnLine_StartTime,OnLine_EndTime,OnLine_IsReturn,UserID,OnLine_MaterialNO,
OnLine_NoticeNO,OnLine_ContractNO,ClassType)'+
'select EngineStreamNO,OnLine_OrderNO,OnLine_CastNO,OnLine_PalletNO,EngineTypeCode,BomName,InstructionName,
OnLine_StartTime,getdate(),OnLine_IsReturn,UserID,OnLine_MaterialNO,OnLine_NoticeNO,OnLine_ContractNO,
ClassType from dbo.OnLineOrder where EngineStreamNO ='''+@EngineStreamNO+''' '--插入ShortHistoryRunBom
set @SQL =@SQL+'insert into ShortHistoryRunBom(EngineStreamNO,StationID,RunBom_MaterialNO,
RunBom_MaterialName,RunBom_Count,RunBom_FigureNO,RunBom_Detection) select EngineStreamNO,StationID,RunBom_MaterialNO,
RunBom_MaterialName,RunBom_Count,RunBom_FigureNO,RunBom_Detection from RunBom where EngineStreamNO='''+@EngineStreamNO+''' '--插入ShortHistoryRunIstruction
set @SQL =@SQL+'insert into ShortHistoryRunInstruction(EngineStreamNO,StationID,RI_StepNO,RI_SubStep,RI_StepDescription,
RI_TechReqment,RI_ToolName) select EngineStreamNO,StationID,RI_StepNO,RI_SubStep,RI_StepDescription,
RI_TechReqment,RI_ToolName from RunInstruction where EngineStreamNO='''+@EngineStreamNO+''' 'end --删除dbo.OnLineOrder
set @SQL =@SQL+' if Exists (select 1 from ShortHistoryOnLineOrder where EngineStreamNO=@EngineStreamNO ) '+
' begin '+
--删除RunBom
' delete from RunBom where EngineStreamNO='''+@EngineStreamNO+''' '+
--删除RunIstruction
' delete from RunInstruction where EngineStreamNO='''+@EngineStreamNO+''' '+
' delete from OnLineOrder where EngineStreamNO='''+@EngineStreamNO+''' '+' end 'BEGIN TRANSACTION
print @SQL
EXEC SP_EXECUTESQL @SQL,N'@EngineStreamNO Nvarchar(50) OUTPUT', @EngineStreamNO output
SELECT @intRowCount=@@ROWCOUNT
-- 插入日志
if (@intRowCount <= 0
begin
insert into Log( ...)
values (...)
end
else
begin
insert into Log( ...)
values (...)
end COMMIT TRANSACTIONRETURN @intRowCount
NeedRollBack:
BEGIN
ROLLBACK TRANSACTION
--SELECT 'NOK' AS NORESULT
RETURN -1
END select存储
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER Procedure [dbo].[Proc_OP2100_WriteFinish]
(
@EngineStreamNO Nvarchar(50)
)
as declare --@strEngineStreamNO Nvarchar(50),
@intErrorCode INT,
@intRowCount INT,
@SQL nvarchar(4000)set @SQL=''if not Exists (select 1 from ShortHistoryOnLineOrder where EngineStreamNO=@EngineStreamNO )
begin
--增加下线订单记录
set @SQL ='insert into ShortHistoryOnLineOrder(EngineStreamNO,OnLine_OrderNO,OnLine_CastNO,OnLine_PalletNO,
EngineTypeCode,BomName,InstructionName,OnLine_StartTime,OnLine_EndTime,OnLine_IsReturn,UserID,OnLine_MaterialNO,
OnLine_NoticeNO,OnLine_ContractNO,ClassType)'+
'select EngineStreamNO,OnLine_OrderNO,OnLine_CastNO,OnLine_PalletNO,EngineTypeCode,BomName,InstructionName,
OnLine_StartTime,getdate(),OnLine_IsReturn,UserID,OnLine_MaterialNO,OnLine_NoticeNO,OnLine_ContractNO,
ClassType from dbo.OnLineOrder where EngineStreamNO ='''+@EngineStreamNO+''' '--插入ShortHistoryRunBom
set @SQL =@SQL+'insert into ShortHistoryRunBom(EngineStreamNO,StationID,RunBom_MaterialNO,
RunBom_MaterialName,RunBom_Count,RunBom_FigureNO,RunBom_Detection) select EngineStreamNO,StationID,RunBom_MaterialNO,
RunBom_MaterialName,RunBom_Count,RunBom_FigureNO,RunBom_Detection from RunBom where EngineStreamNO='''+@EngineStreamNO+''' '--插入ShortHistoryRunIstruction
set @SQL =@SQL+'insert into ShortHistoryRunInstruction(EngineStreamNO,StationID,RI_StepNO,RI_SubStep,RI_StepDescription,
RI_TechReqment,RI_ToolName) select EngineStreamNO,StationID,RI_StepNO,RI_SubStep,RI_StepDescription,
RI_TechReqment,RI_ToolName from RunInstruction where EngineStreamNO='''+@EngineStreamNO+''' 'end --删除dbo.OnLineOrder
set @SQL =@SQL+' if Exists (select 1 from ShortHistoryOnLineOrder where EngineStreamNO=@EngineStreamNO ) '+
' begin '+
--删除RunBom
' delete from RunBom where EngineStreamNO='''+@EngineStreamNO+''' '+
--删除RunIstruction
' delete from RunInstruction where EngineStreamNO='''+@EngineStreamNO+''' '+
' delete from OnLineOrder where EngineStreamNO='''+@EngineStreamNO+''' '+' end 'BEGIN TRANSACTION
print @SQL
EXEC SP_EXECUTESQL @SQL,N'@EngineStreamNO Nvarchar(50) OUTPUT', @EngineStreamNO output
SELECT @intRowCount=@@ROWCOUNT
-- 插入日志
if (@intRowCount <= 0
begin
insert into Log( ...)
values (...)
end
else
begin
insert into Log( ...)
values (...)
end COMMIT TRANSACTIONRETURN @intRowCount
NeedRollBack:
BEGIN
ROLLBACK TRANSACTION
--SELECT 'NOK' AS NORESULT
RETURN -1
END select存储
--不过,你的代码。有2个建议:
--#1.用OUTPUT方式记录日志,好处:删除和备份属于一个事务.
DELETE RunBom
OUTPUT
DELETED.EngineStreamNO ,
DELETED.StationID ,
DELETED.RunBom_MaterialNO ,
DELETED.RunBom_MaterialName ,
DELETED.RunBom_Count ,
DELETED.RunBom_FigureNO ,
DELETED.RunBom_Detection
INTO ShortHistoryRunBom
(
EngineStreamNO ,
StationID ,
RunBom_MaterialNO ,
RunBom_MaterialName ,
RunBom_Count ,
RunBom_FigureNO ,
RunBom_Detection
)
WHERE EngineStreamNO = '123456789'--#2.用TRY...CATCH...方式处理异常,尽量不要标签方式 NeedRollBack:
BEGIN TRY
BEGIN TRAN
COMMIT TRAN
END TRY
begin CATCH
ROLLBACK TRAN
SELECT ERROR_MESSAGE()
END CATCH