create procedure spCorrectTermWholeLife
as 
set nocount on
begin

  declare @PolicyPropInternalNo int,
          @Age int

  declare tmpCursor cursor Local for 
  select pp.PolicyPropInternalNo, 
    case 
      when (DateDiff(mm, Member.Birthdate, it.date) %  12) >= 6 then abs(DateDiff(mm, Member.Birthdate, it.date) / 12) + 1
      else abs(DateDiff(mm, Member.Birthdate, it.date) / 12)
    end age
    from PolicyProposal pp
    Join Member on pp.HolderInternalNo = Member.MemberInternalNo
    Join InsuranceTransaction it On pp.PolicyPropInternalNo = it.TransactionInternalNo
   where PlanInternalno = 5

  open tmpCursor
  fetch tmpcursor into @PolicyPropInternalNo, @Age

  While (@@Fetch_status = 0) begin
    if ((80 - @Age) > 0) begin
      Update PolicyProposal Set Term = 80 - @Age Where PolicyPropInternalNo = @PolicyPropInternalNo and Term <> (80-@Age)
    end
    fetch next from tmpcursor into @PolicyPropInternalNo, @Age
  end

  close tmpCursor
  deallocate tmpCursor
end

