create procedure pr_ModifyHQSchedule--生成总部计划
@scheduleName varchar(50),
@scheduleType varchar(1),
@scheduleCreater varchar(38),
@yearMonth varchar(10),
@scheduleData varchar(100)
as
DECLARE @PointerPrev int
DECLARE @PointerCurr int
DECLARE @TId varchar(10)
declare @guid varchar(100)
declare @scheduleDay int
declare @tdate datetime
Set @PointerPrev=1
SET @scheduleDay=0--将具体日期初始化为0
---先处理主表T_HQ_Schedule的数据
set @guid=newid()
if((select count(1) from T_HQ_Schedule
where HQ_Schedule_Name=@yearMonth)>0)
begin
delete from T_HQ_Schedule
where HQ_Schedule_Date=@yearMonth
end
if((select count(1) from T_HQ_Schedule_Date_Record
where cast(year(Schedule_Date) as varchar(10))+cast(month(Schedule_Date) as varchar(10))=@yearMonth
)>0)
begin
delete from T_HQ_Schedule_Date_Record
where cast(year(Schedule_Date) as varchar(10))+cast(month(Schedule_Date) as varchar(10))=@yearMonth
end
insert into T_HQ_Schedule(HQ_Schedule_Guid,HQ_Schedule_Name,Schedule_Class,Record_Creator,
Is_Published,Record_CreatorDateTime,HQ_Schedule_Date)
values(@guid,@scheduleName,@scheduleType,@scheduleCreater,'N',getdate(),@yearMonth)
---处理明细表T_HQ_Schedule_Date_Record的数据
while (@PointerPrev < LEN(@scheduleData))
Begin
Set @PointerCurr=CharIndex(',',@scheduleData,@PointerPrev)
if(@PointerCurr>0)
Begin
set @TId=SUBSTRING(@scheduleData,@PointerPrev,@PointerCurr-@PointerPrev)
------
set @scheduleDay=@scheduleDay+1
set @tdate = cast( substring(@yearMonth,1,4)+'-'+substring(@yearMonth,5,len(@yearMonth) - 4)+'-'
+ cast(@scheduleDay as varchar(10) as datetime)
insert into T_HQ_Schedule_Date_Record(HQ_Schedule_Date_Guid,HQ_Schedule_Guid,Schedule_Date,
Schedule_Content,Record_Creator,Record_Creator,Record_CreatorDateTime)
values(newid(),@guid,@tdate,@TId,@scheduleCreater,getdate())
SET @PointerPrev = @PointerCurr+1
End
else
Break
End
--处理最后一个,因为最后一个后面没有逗号,所以在循环中跳出,需另外再处理
set @scheduleDay=@scheduleDay+1
set @TId=SUBSTRING(@scheduleData,@PointerPrev,LEN(@scheduleData)-@PointerPrev+1)
set @tdate = cast( substring(@yearMonth,1,4)+'-'+substring(@yearMonth,5,len(@yearMonth) - 4)+'-'
+ cast(@scheduleDay as varchar(10) as datetime)
insert into T_HQ_Schedule_Date_Record(HQ_Schedule_Date_Guid,HQ_Schedule_Guid,Schedule_Date,
Schedule_Content,Record_Creator,Record_Creator,Record_CreatorDateTime)
values(newid(),@guid,@tdate,@TId,@scheduleCreater,getdate())
