老大,太长啦。贴出来,大家可别头晕啊。里面用了很多临时表。小弟写存储过程没啥经验,大大大们看代码有什么明显缺陷一定要指出来啊。SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GOALTER PROCEDURE [dbo].[Coolioo_BuildWIPNoToAll] ( @sArr_No varchar(10) = '', @sGF_No varchar(30) = '', @exeModel int = 0 -- 0:[Default, Rebuild All]; 1:[Append] ) AS if object_id('tempdb.dbo.#UniRelative') is not null drop table #UniRelative CREATE TABLE #UniRelative ( [Arr_No] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL, [GF_No] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ) if object_id('tempdb.dbo.#WIPNoScope') is not null drop table #WIPNoScope CREATE TABLE #WIPNoScope ( [WIPNo] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY, [Arr_No] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL, [GF_No] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL, [RouteID] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL )if object_id('tempdb.dbo.#WIPNoRouteScope') is not null drop table #WIPNoRouteScope CREATE TABLE #WIPNoRouteScope ( [WIPNo] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY, [Arr_No] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL, [GF_No] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL, [RouteID] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL )-- Get Arr_GF_No Scope exec Coolioo_GetSimuScope @sArr_No, @sGF_No-- Search Append Scope if(1=@exeModel) delete #UniRelative from #UniRelative u join wipnotoall w on '#'+u.arr_no+'#|#'+u.gf_no+'#'=w.arr_gf_no where w.routeid='009'if not EXISTS (Select * from #UniRelative) return-- Init WIPNoScope insert #WIPNoScope (WIPNo, RouteID) -- Get Non-Uni WIPNo select tptr.WIPNo, tptr.RouteID from #UniRelative u join TempPatternTechnicsRoute tptr on tptr.arr_no=u.arr_no and tptr.gf_no=u.gf_no union -- Get Uni WIPNo SELECT distinct ptw.WIPNo, right(ptw.WIPNo, 3) FROM PatternToWIP ptw join #UniRelative u on ptw.arr_no=u.arr_no and ptw.gf_no=u.gf_no join TempPatternTechnicsRoute tptr ON ptw.WIPNo = tptr.WIPNoif not EXISTS (Select * from #WIPNoScope) return -- Get temp Arr_No, GF_No update #WIPNoScope set Arr_no=tptr.Arr_no, gf_no=tptr.gf_no from #WIPNoScope ws join TempPatternTechnicsRoute tptr on ws.wipno=tptr.wipno update #WIPNoScope set Arr_no=ptw.Arr_no, gf_no=ptw.gf_no from #WIPNoScope ws join PatternToWIP ptw on ws.wipno=ptw.wipno --[Start to operate WIPNoToAll from here]--Append New WIPNo into WIPNoToAll insert wipnotoall (wipno, Arr_GF_No, IsAssistant, IsEmergent, PreWIPID) select wipno, '#'+ws.Arr_no+'#|#'+RTRIM(ws.GF_no)+'#', 0, 0, '0' from #WIPNoScope ws where not exists (select * from wipnotoall wnta where wnta.wipno=ws.wipno) -- Get RouteID update wipnotoall set RouteID=ws.RouteID from wipnotoall wnta join #WIPNoScope ws on wnta.wipno=ws.wipno -- Create #ProcessTable to get followWIPNo, PreWIPNo, Arr_GF_No if object_id('tempdb.dbo.#ProcessTable') is not null drop table #ProcessTable CREATE TABLE #ProcessTable ( [KeyStr] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL, [Value] [varchar] (1500) COLLATE Chinese_PRC_CI_AS NULL, [ProcessStep] int identity(1,1) ) -- Get FollowWIPID -- Single FollowWIPNo update wipnotoall set wipnotoall.FollowWIPID=tptr.FollowWIPID from #WIPNoScope ws join wipnotoall wnta on wnta.wipno=ws.wipno join TempPatternTechnicsRoute tptr on wnta.wipno=tptr.wipno where tptr.FollowWIPID<>'false' -- Muiti FollowWIPNo declare @sValueBuffer varchar(1500), @sKeyBuffer varchar(200) select @sValueBuffer='', @sKeyBuffer=''--truncate table #ProcessTable --For debuginsert #ProcessTable(KeyStr, Value) select FWIPNo, FollowWIPID from #WIPNoScope ws join TempPTRouteSupplement tps on tps.FWIPNo=ws.wipno order by WIPNo, FollowWIPID--select * from #ProcessTable -For Debugupdate #ProcessTable set @sValueBuffer = Value = case @sKeyBuffer when KeyStr then @sValueBuffer+',' else ''end+Value, @sKeyBuffer = KeyStr--select * from #ProcessTable --For Debugupdate wipnotoall set wipnotoall.FollowWIPID=result.FollowWIPNo from wipnotoall wnta join (select KeyStr as wipno, Value as FollowWIPNo from #ProcessTable join (select max(ProcessStep) MaxStep from #ProcessTable group by KeyStr) x on ProcessStep = MaxStep) result on wnta.wipno=result.wipno-- Get Is Emergent if object_id('tempdb.dbo.#T1') is not null drop table #T1 select distinct pp.Arr_No, pp.gf_no, pp.IsEmergent into #T1 from #UniRelative u join POMS_PInfo pp on pp.arr_no=u.arr_no and pp.gf_no=u.gf_no where IsEmergent=1update wipnotoall set IsEmergent=0 from wipnotoall wnta join #WIPNoScope ws on wnta.wipno=ws.wipno update wipnotoall set IsEmergent=1 from #WIPNoScope ws join wipnotoall wnta on wnta.wipno=ws.wipno join #T1 on #T1.arr_no=ws.arr_no and #T1.gf_no=ws.gf_no if object_id('tempdb.dbo.#T1') is not null drop table #T1-- Get DueDate and CuttingPlanDate update wipnotoall set DueDate=pg.DueDate, CuttingPlanDate=pg.CuttingPlanDate from #WIPNoScope ws join wipnotoall wnta on wnta.wipno=ws.wipno join POMS_GPInfo pg on pg.arr_no=ws.arr_no and pg.gf_no=ws.gf_no -- Get Assistant --Collect 外协 and 外发 into TempTable if object_id('tempdb.dbo.#T2') is not null drop table #T2 select tptr.wipno, tptr.Re into #T2 from #WIPNoScope ws join TempPatternTechnicsRoute tptr on ws.wipno=tptr.wipno where tptr.re='外协' or tptr.re='外发' -- Set IsAssistant update wipnotoall set IsAssistant=1 from #T2 join wipnotoall wnta on #T2.wipno=wnta.wipno -- Set 外协 declare @sMaxMPSNo varchar (12) Select top 1 @sMaxMPSNo=MPSNo from MPS_History order by MPSNo DESCupdate wipnotoall set AssistPBTime=map.PlanBDate, AssistPETime=map.PlanEDate from #T2 join wipnotoall wnta on wnta.wipno=#T2.wipno join MPS_AssistPlan map on map.wipno=wnta.wipno where MPSNo=@sMaxMPSNo and #T2.re='外协'/* -- Set 外发 update wipnotoall set AssistPBTime=mop.PlanBDate, AssistPETime=mop.PlanEDate from wipnotoall wnta join #TempTable tt on wnta.wipno=tt.wipno join MPS_OEMPlan mop on mop.wipno=wnta.wipno where tt.re='外发' */ if object_id('tempdb.dbo.#T2') is not null drop table #T2..............以下略去80%
GO
SET ANSI_NULLS OFF
GOALTER PROCEDURE [dbo].[Coolioo_BuildWIPNoToAll]
(
@sArr_No varchar(10) = '',
@sGF_No varchar(30) = '',
@exeModel int = 0 -- 0:[Default, Rebuild All]; 1:[Append]
)
AS if object_id('tempdb.dbo.#UniRelative') is not null drop table #UniRelative
CREATE TABLE #UniRelative
(
[Arr_No] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL,
[GF_No] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL
)
if object_id('tempdb.dbo.#WIPNoScope') is not null drop table #WIPNoScope
CREATE TABLE #WIPNoScope
(
[WIPNo] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY,
[Arr_No] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL,
[GF_No] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL,
[RouteID] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL
)if object_id('tempdb.dbo.#WIPNoRouteScope') is not null drop table #WIPNoRouteScope
CREATE TABLE #WIPNoRouteScope
(
[WIPNo] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY,
[Arr_No] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL,
[GF_No] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL,
[RouteID] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL
)-- Get Arr_GF_No Scope
exec Coolioo_GetSimuScope @sArr_No, @sGF_No-- Search Append Scope
if(1=@exeModel) delete #UniRelative from #UniRelative u join wipnotoall w on '#'+u.arr_no+'#|#'+u.gf_no+'#'=w.arr_gf_no
where w.routeid='009'if not EXISTS (Select * from #UniRelative) return-- Init WIPNoScope
insert #WIPNoScope (WIPNo, RouteID)
-- Get Non-Uni WIPNo
select tptr.WIPNo, tptr.RouteID from #UniRelative u join TempPatternTechnicsRoute tptr on tptr.arr_no=u.arr_no and tptr.gf_no=u.gf_no
union
-- Get Uni WIPNo
SELECT distinct ptw.WIPNo, right(ptw.WIPNo, 3) FROM PatternToWIP ptw join #UniRelative u on ptw.arr_no=u.arr_no and ptw.gf_no=u.gf_no
join TempPatternTechnicsRoute tptr ON ptw.WIPNo = tptr.WIPNoif not EXISTS (Select * from #WIPNoScope) return
-- Get temp Arr_No, GF_No
update #WIPNoScope set Arr_no=tptr.Arr_no, gf_no=tptr.gf_no from #WIPNoScope ws join TempPatternTechnicsRoute tptr on ws.wipno=tptr.wipno
update #WIPNoScope set Arr_no=ptw.Arr_no, gf_no=ptw.gf_no from #WIPNoScope ws join PatternToWIP ptw on ws.wipno=ptw.wipno
--[Start to operate WIPNoToAll from here]--Append New WIPNo into WIPNoToAll
insert wipnotoall (wipno, Arr_GF_No, IsAssistant, IsEmergent, PreWIPID) select wipno, '#'+ws.Arr_no+'#|#'+RTRIM(ws.GF_no)+'#', 0, 0, '0' from #WIPNoScope ws where not exists (select * from wipnotoall wnta where wnta.wipno=ws.wipno)
-- Get RouteID
update wipnotoall set RouteID=ws.RouteID from wipnotoall wnta join #WIPNoScope ws on wnta.wipno=ws.wipno
-- Create #ProcessTable to get followWIPNo, PreWIPNo, Arr_GF_No
if object_id('tempdb.dbo.#ProcessTable') is not null drop table #ProcessTable
CREATE TABLE #ProcessTable
(
[KeyStr] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL,
[Value] [varchar] (1500) COLLATE Chinese_PRC_CI_AS NULL,
[ProcessStep] int identity(1,1)
)
-- Get FollowWIPID
-- Single FollowWIPNo
update wipnotoall set wipnotoall.FollowWIPID=tptr.FollowWIPID from #WIPNoScope ws join wipnotoall wnta on wnta.wipno=ws.wipno
join TempPatternTechnicsRoute tptr on wnta.wipno=tptr.wipno
where tptr.FollowWIPID<>'false' -- Muiti FollowWIPNo
declare @sValueBuffer varchar(1500), @sKeyBuffer varchar(200)
select @sValueBuffer='', @sKeyBuffer=''--truncate table #ProcessTable --For debuginsert #ProcessTable(KeyStr, Value)
select FWIPNo, FollowWIPID
from #WIPNoScope ws join TempPTRouteSupplement tps on tps.FWIPNo=ws.wipno
order by WIPNo, FollowWIPID--select * from #ProcessTable -For Debugupdate #ProcessTable
set @sValueBuffer = Value = case @sKeyBuffer when KeyStr then @sValueBuffer+',' else ''end+Value,
@sKeyBuffer = KeyStr--select * from #ProcessTable --For Debugupdate wipnotoall set wipnotoall.FollowWIPID=result.FollowWIPNo from wipnotoall wnta join
(select KeyStr as wipno, Value as FollowWIPNo from #ProcessTable
join (select max(ProcessStep) MaxStep from #ProcessTable group by KeyStr) x
on ProcessStep = MaxStep) result on wnta.wipno=result.wipno-- Get Is Emergent
if object_id('tempdb.dbo.#T1') is not null drop table #T1
select distinct pp.Arr_No, pp.gf_no, pp.IsEmergent into #T1 from #UniRelative u join POMS_PInfo pp on pp.arr_no=u.arr_no and pp.gf_no=u.gf_no
where IsEmergent=1update wipnotoall set IsEmergent=0 from wipnotoall wnta join #WIPNoScope ws on wnta.wipno=ws.wipno
update wipnotoall set IsEmergent=1 from #WIPNoScope ws join wipnotoall wnta on wnta.wipno=ws.wipno
join #T1 on #T1.arr_no=ws.arr_no and #T1.gf_no=ws.gf_no
if object_id('tempdb.dbo.#T1') is not null drop table #T1-- Get DueDate and CuttingPlanDate
update wipnotoall set DueDate=pg.DueDate, CuttingPlanDate=pg.CuttingPlanDate from #WIPNoScope ws join wipnotoall wnta on wnta.wipno=ws.wipno
join POMS_GPInfo pg on pg.arr_no=ws.arr_no and pg.gf_no=ws.gf_no -- Get Assistant
--Collect 外协 and 外发 into TempTable
if object_id('tempdb.dbo.#T2') is not null drop table #T2
select tptr.wipno, tptr.Re into #T2 from #WIPNoScope ws join TempPatternTechnicsRoute tptr on ws.wipno=tptr.wipno
where tptr.re='外协' or tptr.re='外发'
-- Set IsAssistant
update wipnotoall set IsAssistant=1 from #T2 join wipnotoall wnta on #T2.wipno=wnta.wipno
-- Set 外协
declare @sMaxMPSNo varchar (12)
Select top 1 @sMaxMPSNo=MPSNo from MPS_History order by MPSNo DESCupdate wipnotoall set AssistPBTime=map.PlanBDate, AssistPETime=map.PlanEDate
from #T2 join wipnotoall wnta on wnta.wipno=#T2.wipno
join MPS_AssistPlan map on map.wipno=wnta.wipno
where MPSNo=@sMaxMPSNo and #T2.re='外协'/*
-- Set 外发
update wipnotoall set AssistPBTime=mop.PlanBDate, AssistPETime=mop.PlanEDate
from wipnotoall wnta join #TempTable tt on wnta.wipno=tt.wipno
join MPS_OEMPlan mop on mop.wipno=wnta.wipno
where tt.re='外发'
*/
if object_id('tempdb.dbo.#T2') is not null drop table #T2..............以下略去80%
2 查询分析器中按Ctrl+2可以看到锁的信息处理超时和设置锁超时持续时间。@@LOCK_TIMEOUT 返回当前会话的当前锁超时设置,单位为毫秒SET LOCK_TIMEOUT 设置允许应用程序设置语句等待阻塞资源的最长时间。当语句等待的时间大于 LOCK_TIMEOUT 设置时,系统将自动取消阻塞的语句,并给应用程序返回"已超过了锁请求超时时段"的 1222 号错误信息你设置一下
SET LOCK_TIMEOUT 1800
一般由事务引起的死锁(阻塞)有哪些??
我的程序就一个线程,数据库也只有我自己使用。