SQL递归处理数据

/*

根据前置任务的时间，修改后续任务的时间

@ParentCyclePlanId：前置任务编号

*/

ALTER Proc ChangeCyclePlanDate

@ParentCyclePlanId int

As

Declare @CyclePlanId Int, @CyclePlanEndTime Char(10), @State Int, @Temp Char(10), @Other Char(10)

--声明游标

Declare CyclePlan_Cursor Cursor Local 

For

Select CyclePlanId, CyclePlanStartTime, CyclePlanEndTime From CyclePlan Where PrepositiveCyclePlan = @ParentCyclePlanId

For Update Of CyclePlanStartTime, CyclePlanEndTime

Open CyclePlan_Cursor

--如果没有后续任务则释放游标，并立即返回

If @@Cursor_Rows = 0

Begin

Close CyclePlan_Cursor

Deallocate CyclePlan_Cursor

Return

End

--递归

Select @CyclePlanEndTime = CyclePlanEndTime From CyclePlan Where CyclePlanId = @ParentCyclePlanId

Fetch CyclePlan_Cursor

Into @CyclePlanId, @Temp, @Other

Select @State = @@Fetch_Status

While @State = 0

Begin

Update CyclePlan Set

CyclePlanStartTime = replace(Convert(Char(10), DateAdd(day, 1, @CyclePlanEndTime), 111), '/', '-'),

CyclePlanEndTime = replace(Convert(Char(10), DateAdd(day, DateDiff(day, @Temp, CyclePlanEndTime) + 1, @CyclePlanEndTime), 111), '/', '-')

Where Current Of CyclePlan_Cursor

Exec ChangeCyclePlanDate @ParentCyclePlanId = @CyclePlanId

Fetch CyclePlan_Cursor

Into @CyclePlanId, @Temp

Select @State = @@Fetch_Status

End

Return