求SQL高手指导一个存储过程的修改

[happydaily]

表，存储过程，测试数据我都建立好了，但由于水平有限，弄得头晕，想请那位高手能够帮忙修改测试下。问题是这样的，我将数据存在一个临时表中，现在通过存储过程将数据转移到正式表中，要转移的数据是时间，这里面涉及一个简单的算法，如果要转移的时间与正式表中（还有个检测条件，也有点复杂）有冲突，那么要对正式表中的时间进行调整，将其向后推迟或截断部分并插入新行，代码写好了但还是有问题，想请高手帮忙看下，涉及问题 多表关联检测exists,游标。有兴趣的可以加我QQ55585811，我把代码给你看下。

[开着拖拉机泡妞]

看起来好复杂，自己写写吧

[Rotel-刘志东]

列出表结构及数据结果。

[happydaily]

存储过程共两个，分为简单版和复杂版。复杂版就是多了几个检测步骤，实质和简单版差不多。

--主表
CREATE TABLE [dbo].[tb_zb](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[bianhao] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[xmname] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[bzr] [int] NULL,	
	[xmlc] [int] NULL
)
--时间总表
CREATE TABLE [dbo].[ERPProjectPhaseTime](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[bianhao] [int] NULL,
	[phase] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
	[starttime] [smalldatetime] NULL,
	[endtime] [smalldatetime] NULL,
	[days] [int] NULL,
	[category] [int] NULL,
	[orderid] [int] NULL)
--临时表
CREATE TABLE [dbo].[ERPProjectTempPhaseTime](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[bianhao] [int] NULL,
	[phase] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
	[starttime] [smalldatetime] NULL,
	[endtime] [smalldatetime] NULL,
	[days] [int] NULL,
	[xmlc] [int] NULL,
	[SerialNumber] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
	[inserttime] [smalldatetime] NULL CONSTRAINT [DF_ERPProjectTempPhaseTime_inserttime]  DEFAULT (getdate()),
	[orderid] [int] NULL,
	[mode] [int] NULL,--模式，标记为0时直接写入ERPProjectPhaseTime不检测冲突，标记为1时检测
	[yskinds] [int] NULL,
	[bzr] [int] NULL,
	[Tempkinds] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL)--标记数据，有zbtime,zjtime,zjphasetime,zjbzr几种，对应本行数据应更新到那个表

对应关系 tb_zb.id=ERPProjectPhaseTime.bianhao=ERPProjectTempPhaseTime.bianhao

[happydaily]

简单版存储过程如下：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ERPAddPhaseTime](@newbianhao int,@xmlc int,@SerialNumber varchar(50),@tempkinds varchar(50), @ReturnValue int output)
AS
declare @bianhao int, @days int ,@endtime smalldatetime ,@starttime smalldatetime ,@category int ,@phase int,@orderid int, @effectrow int
begin
Declare Timecur cursor for select [bianhao],[phase],[starttime],[endtime],[days],case when xmlc<5 then 1 else xmlc-3 end as  [category],[orderid] from ERPProjectTempPhaseTime where SerialNumber=@SerialNumber and bianhao=@newbianhao and xmlc=@xmlc and tempkinds=@tempkinds and mode=1;
open Timecur
fetch next from Timecur into  @bianhao,@phase,@starttime ,@endtime, @days ,@category ,@orderid 
while (@@fetch_status=0)
begin	
	--检测冲突,存在冲突则时间向后顺延,不存在则直接写入数据
	select @effectrow=count(1) from ERPProjectPhaseTime as a where (@starttime between starttime and endtime or @endtime between starttime and endtime) and exists(select 1 from tb_zb where id=a.bianhao and bzr=(select bzr from tb_zb where id=@bianhao))
	if(@effectrow>0)
	begin
	--在写入时间之后的数据都向后延时。代码算是半成功，应该是冲突的那一行仅向后顺延starttime=dateadd(day,1,@endtime),endtime=dateadd(day,@days,@endtime),不然时间出现不连续的情况
	update a set starttime=dateadd(day,@days,starttime),endtime=dateadd(day,@days,endtime) from ERPProjectPhaseTime as a where starttime>=@starttime and category=@category and exists(select 1 from tb_zb where id=a.bianhao and bzr=(select bzr from tb_zb where id=@bianhao))
	--更新结束时间在表时间之内的部分，此部分代码测试后无效。
	--update a set starttime=DATEADD(DAY,1,@endtime),endtime=DATEADD(DAY,days,@endtime) from ERPProjectPhaseTime as a where (@endtime between starttime and endtime) and category=@category and exists(select 1 from tb_zb where id=a.bianhao and bzr=(select bzr from tb_zb where id=@bianhao))
	--更新开始时间在表时间之内的部分
	insert into ERPProjectPhaseTime(bianhao, phase,starttime,endtime,days,category, orderid) select bianhao, phase,DATEADD(day ,1,@endtime),DATEADD(day,@days,endtime),DATEDIFF(day,@starttime,endtime)+1,category, orderid+1 from ERPProjectPhaseTime where @starttime > starttime and @starttime <=endtime and category=@category
	update a set days=DATEDIFF(day,starttime,@starttime), endtime=dateadd(day,-1, @starttime) from  ERPProjectPhaseTime as a where(@starttime between starttime and endtime) and category=@category and  exists(select 1 from tb_zb where id=a.bianhao and bzr=(select bzr from tb_zb where id=@bianhao))
	end
	--写入本次设定的数据
	insert into ERPProjectPhaseTime(bianhao, phase,starttime,endtime,days,category, orderid) values(@bianhao,@phase,@starttime,@endtime,@days,@category,1);
	
fetch next from Timecur into @bianhao,@phase,@starttime ,@endtime, @days ,@category ,@orderid 
end
close Timecur --关闭游标
deallocate Timecur
set @ReturnValue=0
return @ReturnValue
end

[happydaily]

复杂版还有表如下：

--分
CREATE TABLE [dbo].[ERPZJPhaseTime](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[bianhao] [int] NOT NULL,
	[phase] [int] NULL,
	[phasename] [nvarchar](200) COLLATE Chinese_PRC_CI_AS NULL,
	[starttime] [smalldatetime] NULL,
	[endtime] [smalldatetime] NULL,
	[days] [int] NULL,
	[bzstate] [int] NULL,
	[shstate] [int] NULL,
	[AchieveState] [int] NULL,
	[category] [int] NULL,
	[orderid] [int] NULL)