存储过程：p_bomEntry （编号：20110630A1438 ）_insertauditentry.bom密码-优快云博客

本文详细解析了一个用于数据库操作的SQL存储过程，包括插入、更新、删除、审核和激活等操作，以及相应的参数说明和流程逻辑。特别关注了在执行过程中可能出现的锁定失败、死锁等问题及解决方案。

USE [db_NLMRPII] GO /****** 对象: StoredProcedure [dbo].[p_bomEntry] 脚本日期: 06/30/2011 14:29:15 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[p_bomEntry] -- ======================================== -- BomEntry -- ======================================== -- 参数: -- 返回值： -- 注：5次失败尝试。（锁定失败、死锁） -- ======================================== -- 测试数据 -- INSERT INTO [db_20101014_Wangy].[dbo].[ICBOMChild]( -- [FBrNo] -- ,[FEntryID] -- ,[FInterID] -- ,[FItemID] -- ,[FAuxQty] -- ,[FQty] -- ,[FScrap] -- ,[FOperSN] -- ,[FOperID] -- ,[FMachinePos] -- ,[FNote] -- ,[FMaterielType] -- ,[FMarshalType] -- ,[FPercent] -- ,[FBeginDay] -- ,[FEndDay] -- ,[FOffSetDay] -- ,[FBackFlush] -- ,[FStockID] -- ,[FSPID] -- ,[FSupply] -- ,[FUnitID] -- ,[FAuxPropID] -- ,[FPDMImportDate] -- ,[FPositionNo] -- ,[FItemSize] -- ,[FItemSuite] -- ,[FNote1] -- ,[FNote2] -- ,[FNote3] -- ,[FHasChar] -- ) -- VALUES( -- 0 -- <FBrNo, varchar(10),> -- ,9 -- <FEntryID, int,> -- ,398956 -- <FInterID, int,> -- ,4192 -- <FItemID, int,> -- ,1 -- <FAuxQty, decimal(28,10),> -- ,2 -- <FQty, decimal(28,10),> /* 数量 */ -- ,0 -- <FScrap, decimal(28,10),> -- ,0 -- <FOperSN, int,> -- ,0 -- <FOperID, int,> -- ,'' -- <FMachinePos, varchar(1000),> -- ,'' -- <FNote, varchar(1000),> -- ,371 -- <FMaterielType, int,> -- ,385 -- <FMarshalType, int,> -- ,100 -- <FPercent, decimal(28,10),> -- ,'1900-1-1' -- <FBeginDay, datetime,> -- ,'2222-2-2' -- <FEndDay, datetime,> -- ,0 -- <FOffSetDay, decimal(28,10),> -- ,1059 -- <FBackFlush, int,> -- ,0 -- <FStockID, int,> -- ,0 -- <FSPID, int,> -- ,0 -- <FSupply, smallint,> -- ,66 -- <FUnitID, int,> -- ,0 -- <FAuxPropID, int,> -- ,null -- <FPDMImportDate, datetime,> -- ,'' -- <FPositionNo, nvarchar(255),> -- ,'' -- <FItemSize, nvarchar(255),> -- ,'' -- <FItemSuite, nvarchar(255),> -- ,'' -- <FNote1, nvarchar(255),> -- ,'' -- <FNote2, nvarchar(255),> -- ,'' -- <FNote3, nvarchar(255),> -- ,0 -- <FHasChar, smallint,> -- ) -- ======================================== ( @action varChar(1000) -- 取值: Insert 、Update 、Delete、Audit、setActive ,@bomId varChar(1000) -- ,@entryId varChar(1000) -- ,@entryIds varChar(1000) -- ,@entryItemId varChar(1000) -- ,@entryQuantity varChar(1000) -- ,@note varChar(1000) -- ) AS -- 定义变量 ------------------------------------------------- DECLARE @Err INT -- 错误编号 ,@ErrCounter INT -- 错误次数 ,@ErrCounterMax INT -- 最大错误次数 ,@code varChar(1000) -- 返回代码 ,@message varChar(1000) -- 返回信息 BEGIN BEGIN TRANSACTION TRANS SET @ErrCounter = 0 SET @ErrCounterMax = 5 set @code = '0' set @message = '成功' LockTimeOutRetry: -- 锁定超时入口 BEGIN TRY -- （1）Insert ------------------------------------------------- IF @action = 'Insert' begin DECLARE @return_value int, @itemId int -- 取主表物料ID select @itemId = f_intItemId from v_bom where f_intBomId = @bomId -- 检查合法性 EXEC @return_value = [dbo].[p_bomEntry_item_validCheck] @itemId = @itemId ,@entryItemId = @entryItemId -- Need to check -- 结果为 0 时，可以插入。 if( @return_value > 0) begin set @code = 201106301105 set @message = '您欲添加的物料不合法，该物料在其 N 级父 Bom 中已经存在，添加操作没有执行！' + 'Invalid item, the item is already exist in his fathers path, action is canceled.' end else INSERT INTO [db_20101014_Wangy].[dbo].[ICBOMChild]( [FBrNo] ,[FEntryID] ,[FInterID] ,[FItemID] ,[FAuxQty] ,[FQty] ,[FScrap] ,[FOperSN] ,[FOperID] ,[FMachinePos] ,[FNote] ,[FMaterielType] ,[FMarshalType] ,[FPercent] ,[FBeginDay] ,[FEndDay] ,[FOffSetDay] ,[FBackFlush] ,[FStockID] ,[FSPID] ,[FSupply] ,[FUnitID] ,[FAuxPropID] ,[FPDMImportDate] ,[FPositionNo] ,[FItemSize] ,[FItemSuite] ,[FNote1] ,[FNote2] ,[FNote3] ,[FHasChar] ) VALUES( 0 -- <FBrNo, varchar(10),> ,@entryId -- <FEntryID, int,> ,@bomId -- <FInterID, int,> ,@entryItemId -- <FItemID, int,> ,@entryQuantity -- <FAuxQty, decimal(28,10),> ,@entryQuantity -- <FQty, decimal(28,10),> /* 数量 */ ,0 -- <FScrap, decimal(28,10),> ,0 -- <FOperSN, int,> ,0 -- <FOperID, int,> ,'' -- <FMachinePos, varchar(1000),> ,'' -- <FNote, varchar(1000),> ,371 -- <FMaterielType, int,> ,385 -- <FMarshalType, int,> ,100 -- <FPercent, decimal(28,10),> ,'1900-1-1' -- <FBeginDay, datetime,> ,'2222-2-2' -- <FEndDay, datetime,> ,0 -- <FOffSetDay, decimal(28,10),> ,1059 -- <FBackFlush, int,> ,0 -- <FStockID, int,> ,0 -- <FSPID, int,> ,0 -- <FSupply, smallint,> ,66 -- <FUnitID, int,> ,0 -- <FAuxPropID, int,> ,null -- <FPDMImportDate, datetime,> ,'' -- <FPositionNo, nvarchar(255),> ,'' -- <FItemSize, nvarchar(255),> ,'' -- <FItemSuite, nvarchar(255),> ,'' -- <FNote1, nvarchar(255),> ,'' -- <FNote2, nvarchar(255),> ,'' -- <FNote3, nvarchar(255),> ,0 -- <FHasChar, smallint,> ) end -- （2）Update ------------------------------------------------- IF @action = 'Update' update [db_20101014_Wangy].[dbo].[ICBOMChild] set "FHasChar" = 0 where FInterID = @bomId and FEntryID = @entryId -- （3）Delete ------------------------------------------------- IF @action = 'Delete' begin DECLARE @delSQL VARCHAR(MAX) set @delSQL = ' Delete [db_20101014_Wangy].[dbo].[ICBOMChild] ' + ' where [fInterId] = ' + @bomId + ' and FEntryID in ( ' + @entryIds + ')' exec( @delSQL ) end -- （4）Audit 批量审核 ------------------------------------------------- IF @action = 'Audit' update [db_20101014_Wangy].[dbo].[ICBOMChild] set "FHasChar" = 0 where FInterID = @bomId and FEntryID = @entryIds -- （3）setActive 批量激活，禁用 ------------------------------------------------- IF @action = 'Active' update [db_20101014_Wangy].[dbo].[ICBOMChild] set "FHasChar" = 0 where FInterID = @bomId and FEntryID = @entryIds END TRY -- 错误检测 ------------------------------------------------- BEGIN CATCH set @Err = @@ERROR IF @Err <> 0 BEGIN ROLLBACK TRANSACTION TRANS GOTO ErrorHandler END END CATCH COMMIT TRANSACTION TRANS -- Success select @code as [code] , @message as [message] RETURN -- 错误处理 ------------------------------------------------- ErrorHandler: IF (@err = 1222 OR @err = 1205) AND @ErrCounter >= @ErrCounterMax Begin RAISERROR('Unable to Lock Data after a few attempts.',16,1) set @code = @err SELECT @message =ERROR_MESSAGE() END IF @err = 1222 OR @err = 1205 -- Lock Timeout / Deadlock BEGIN WAITFOR DELAY '00:00:00.25' SET @ErrCounter = @ErrCounter + 1 GOTO LockTimeOutRetry END -- else unknown error set @code = @err SELECT @message = ERROR_MESSAGE() -- RAISERROR( @message ,16,1) select @code as code, @message as message END RETURN -- end