question

Hi I have a proc(pasted as a query) which use cursor and is running very slow.I have to process 18000 records for one @ Rp.Is there any wasy I can use a while loop instead. Can any one help Please? Very Urgent... Thanks, Hanoi declare @RP varchar(6) Set @Rp= '042012' Declare @Temp Table ( agenid int, ApplicationSequence int, SubBeginDate datetime, SubEndDate datetime, AssistanceStart datetime ) Declare @applicationDate datetime, @assistanceStart datetime, @applicationSequence int, @subBeginDate datetime, @subEndDate datetime, @oneLessSeq int, @agenid int, @PreSubEndDate datetime, @preSubBeginDate datetime, @compareBeginDate datetime, @subsidyBeginDate datetime, @PreAssistanceStart datetime, @compareEndDate datetime Insert into @Temp ( agenid, ApplicationSequence, SubBeginDate, SubEndDate ) Select distinct A.ApplicantgenId, A.ApplicationSequence, SubsidyBeginDate, SubsidyEndDate from APP A Inner Join Fam f on f.Uid = A.AGenid where f.Rptperiod= @RP and f.Uid = A.AGenID and A.ApplicationSequence = f.ApplicationSequence --Select * from @Temp DECLARE X CURSOR FOR SELECT agenid, applicationsequence, SubBeginDate,SubEndDate FROM @Temp order by agenid OPEN X FETCH NEXT FROM X INTO @agenid, @applicationsequence, @SubBeginDate,@SubEndDate WHILE @@FETCH_STATUS = 0 BEGIN set @assistanceStart = @SubBeginDate set @oneLessSeq = @applicationSequence if (@applicationSequence = 1) Begin Set @assistanceStart = @SubBeginDate End else Begin select @compareBeginDate = @subBeginDate while (@oneLessSeq >1 ) Begin set @oneLessSeq = @oneLessSeq - 1 set @preSubEndDate = null set @preSubBeginDate = null set @PreAssistanceStart = null select @preSubBeginDate = SubsidyBeginDate,@preSubEndDate = SubsidyEndDate,@PreAssistanceStart= SubsidyBeginDate from App A ,Fam f where A.AGenID = f.Uid and A.applicationsequence = @oneLessSeq and f.RptPeriod = @RP and A.AGenID = @agenid if(@PreSubEndDate is not null and datediff(day,@presubEndDate,@compareBeginDate)<= 90)---no gap Begin set @compareBeginDate = @presubBeginDate set @compareEndDate = @presubEndDate set @AssistanceStart = @PreAssistanceStart End else if(@PreSubEndDate is not null and datediff(day,@preSubEndDate ,@compareBeginDate)>90)--is there is a gap begin Set @oneLessSeq = 1 break; end else set @oneLessSeq = @oneLessSeq End End Update @Temp Set AssistanceStart = @AssistanceStart where agenid = @agenid and applicationsequence = @applicationSequence FETCH NEXT FROM X INTO @agenid, @applicationsequence, @SubBeginDate,@SubEndDate END CLOSE applicants_cursor DEALLOCATE X Select AGenid, ApplicationSequence, Convert(varchar,SubBeginDate) as SubsidyBeginDate, Convert(varchar,SubEndDate) as SubsidyEndDate, Convert(varchar,AssistanceStart) as AssistanceStart from @Temp