Create procedure spuPolicyProposalHolder(  
  @OldMemberInternalNo int = Null,   
  @NewMemberInternalNo int = Null,  
  @DeleteOld bit = Null)  
  
as  
set nocount on  
begin  
  Begin Transaction  
  
    if exists(select * from BestCombinationPlan where ProposerInternalNo = @OldMemberInternalNo) begin   
      update BestCombinationPlan  
      set  
        ProposerInternalNo  = @NewMemberInternalNo  
      where  
        ProposerInternalNo = @OldMemberInternalNo  
  
      if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end  
        return -1  
      end  
    end  
  
    if exists(select * from BestCombinationPlan where JointHolderInternalNo = @OldMemberInternalNo) begin   
      update BestCombinationPlan  
      set  
        JointHolderInternalNo  = @NewMemberInternalNo  
      where  
        JointHolderInternalNo = @OldMemberInternalNo  
  
      if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end  
        return -1  
      end  
    end  
  
    if exists(select * from BestCombinationPlan where HolderInternalNo = @OldMemberInternalNo) begin   
      update BestCombinationPlan  
      set  
        HolderInternalNo  = @NewMemberInternalNo  
      where  
        HolderInternalNo = @OldMemberInternalNo  
  
      if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end  
        return -1  
      end  
    end  
  
    if exists(select * from BestCombinationNeed where MemberInternalNo = @OldMemberInternalNo) begin   
      update BestCombinationNeed  
      set  
        MemberInternalNo  = @NewMemberInternalNo  
      where  
        MemberInternalNo = @OldMemberInternalNo  
  
      if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end  
        return -1  
      end  
    end  
  
    if exists(select * from BestCombinationMember where MemberInternalNo = @OldMemberInternalNo) begin   
      update BestCombinationMember  
      set  
        MemberInternalNo  = @NewMemberInternalNo  
      where  
        MemberInternalNo = @OldMemberInternalNo  
  
      if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end  
        return -1  
      end  
    end  
  
    if exists(select * from Combination where MemberInternalNo = @OldMemberInternalNo) begin   
      update Combination  
      set  
        MemberInternalNo  = @NewMemberInternalNo  
      where  
        MemberInternalNo = @OldMemberInternalNo  
  
      if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end  
        return -1  
      end  
    end  
  
  
    if exists(select * from PolicyProposalRider where HolderInternalNo = @OldMemberInternalNo) begin   
      update PolicyProposalRider  
      set  
        HolderInternalNo  = @NewMemberInternalNo  
      where  
        HolderInternalNo = @OldMemberInternalNo  
  
      if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end  
        return -1  
      end  
    end  

    if exists(select * from PolicyProposal where HolderInternalNo = @OldMemberInternalNo) begin   
      update PolicyProposal  
      set  
        HolderInternalNo  = @NewMemberInternalNo  
      where  
        HolderInternalNo = @OldMemberInternalNo  
  
      if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end  
        return -1  
      end  
    end  

    if exists(select * from PolicyProposal where JointHolderInternalNo = @OldMemberInternalNo) begin   
      update PolicyProposal  
      set  
        JointHolderInternalNo = @NewMemberInternalNo  
      where  
        JointHolderInternalNo = @OldMemberInternalNo  
  
      if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end  
        return -1  
      end  
    end  
  
  if exists(select * from PolicyProposal where ProposerInternalNo = @OldMemberInternalNo) begin   
    update PolicyProposal          
    set  
      ProposerInternalNo = @NewMemberInternalNo  
    where  
      ProposerInternalNo = @OldMemberInternalNo  
      if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end  
        return -1  
      end  
  end   
  
  if exists(select * from PolicyProposalHolder where HolderInternalNo = @OldMemberInternalNo) begin   
    update PolicyProposalHolder  
    set  
      HolderInternalNo  = @NewMemberInternalNo  
    where  
      HolderInternalNo = @OldMemberInternalNo  
      if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end  
        return -1  
      end  
  end  
  
  if exists(select * from FamilyHistory where MemberInternalNo = @OldMemberInternalNo) begin   
    update FamilyHistory  
    set  
      MemberInternalNo  = @NewMemberInternalNo  
    where  
      memberInternalNo = @OldMemberInternalNo  
      if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end  
        return -1  
      end  
   end  
  
  if exists(select * from Proposal where HolderInternalNo = @OldMemberInternalNo) begin   
   update Proposal  
    set  
      HolderInternalNo  = @NewMemberInternalNo  
    where  
      HolderInternalNo = @OldMemberInternalNo  
     if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end  
        return -1  
      end  
   end  
  
  
  if exists(select * from Proposal where ProposerInternalNo = @OldMemberInternalNo) begin   
    update Proposal  
    set  
      ProposerInternalNo  = @NewMemberInternalNo  
    where  
      ProposerInternalNo = @OldMemberInternalNo  
    if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end   
        return -1  
      end  
   end  
  
-- jointholder ?  
  if exists(select * from ProposalFemale where HusbandInternalNo = @OldMemberInternalNo) begin   
   update ProposalFemale  
    set  
      HusbandInternalNo  = @NewMemberInternalNo  
    where  
      HusbandInternalNo = @OldMemberInternalNo  
    if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end   
        return -1  
      end  
   end  
  
  if exists(select * from AnnuityQuotation where HolderInternalNo = @OldMemberInternalNo) begin   
   update AnnuityQuotation  
    set  
      HolderInternalNo  = @NewMemberInternalNo  
    where  
      HolderInternalNo = @OldMemberInternalNo  
    if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end   
        return -1  
      end  
   end  
  
  if exists(select * from AnnuityQuotation where JointHolderInternalNo = @OldMemberInternalNo) begin   
   update AnnuityQuotation  
    set  
      JointHolderInternalNo  = @NewMemberInternalNo  
    where  
      JointHolderInternalNo = @OldMemberInternalNo  
    if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end   
        return -1  
      end  
   end  
  
  if exists(select * from Appointment where MemberInternalNo = @OldMemberInternalNo) begin   
   update Appointment  
    set  
      MemberInternalNo  = @NewMemberInternalNo  
    where  
      MemberInternalNo = @OldMemberInternalNo  
    if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
rollback tran  
        end   
        return -1  
      end  
   end    
  
  if exists(select * from QuotationDetailRider where HolderInternalNo = @OldMemberInternalNo) begin   
    update QuotationDetailRider  
    set  
      HolderInternalNo  = @NewMemberInternalNo  
    where  
      HolderInternalNo = @OldMemberInternalNo  
      
    if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end   
        return -1  
      end  
   end    
  
  if exists(select * from QuotationDetailHolder where HolderInternalNo = @OldMemberInternalNo) begin   
    update QuotationDetailHolder  
    set  
      HolderInternalNo  = @NewMemberInternalNo  
    where  
      HolderInternalNo = @OldMemberInternalNo  
      
    if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end   
        return -1  
      end  
   end    
  
 if exists(select * from Quotation where HolderInternalNo = @OldMemberInternalNo) begin   
   update Quotation  
    set  
      HolderInternalNo  = @NewMemberInternalNo  
    where  
      HolderInternalNo = @OldMemberInternalNo  
    if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
rollback tran  
        end   
        return -1  
      end  
   end    
  
 if exists(select * from Quotation where JointHolderInternalNo = @OldMemberInternalNo) begin   
   update Quotation  
    set  
      JointHolderInternalNo  = @NewMemberInternalNo  
    where  
      JointHolderInternalNo = @OldMemberInternalNo  
    if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end   
        return -1  
      end  
   end    
  
  if exists(select * from Quotation where ProposerInternalNo = @OldMemberInternalNo) begin   
    update Quotation  
    set  
      ProposerInternalNo  = @NewMemberInternalNo  
    where  
      ProposerInternalNo = @OldMemberInternalNo  
    if @@rowCount < 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end   
        return -1  
      end  
   end    
  
  /*-----------------------------------Transfer From SMS Queue -------------------*/  
  if exists(select * from SmsQueue Where MemberInternalno = @OldMemberInternalNo) begin  
    update SmsQueue set MemberInternalno = @NewMemberInternalNo Where MemberInternalNo = @OldMemberInternalNo  
    if @@RowCount < 1 begin  
      if @@Trancount = 1 begin  
        rollback tran  
      end  
      return -1  
    end   
  end  
  
  /* --------------------------- Transfer from bank Details -------------------------------*/  
  if exists(select * From ClientBankDetail Where MemberInternalNo = @OldMemberInternalNo) begin  
    update ClientBankDetail Set MemberInternalno = @NewMemberInternalNo Where MemberInternalNo = @OldMemberInternalNo  
     
    if @@RowCount < 1 begin  
      if @@Trancount = 1 begin  
        rollback tran  
      end  
      return -1  
    end   
  end  
  
  
  if @DeleteOld = 1 begin  
    if exists(select * from EmailSMSExcludeMember where MemberInternalNo = @OldMemberInternalNo) begin   
      Delete from EmailSMSExcludeMember where MemberInternalNo = @OldMemberInternalNo  
      if @@rowCount <> 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end   
        return -1  
      end  
    end  
  
    if exists(select * from Client where MemberInternalNo = @OldMemberInternalNo) begin   
      Delete from Client where MemberInternalNo = @OldMemberInternalNo  
      if @@rowCount <> 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end   
        return -1  
      end  
    end    
   
    if exists(select * from ProspectiveClient where ProspectiveClientInternalNo = @OldMemberInternalNo) begin   
      Delete from ProspectiveClient where ProspectiveClientInternalNo = @OldMemberInternalNo  
        if @@rowCount <> 1 begin  
          if @@TranCount = 1 begin  
            rollback tran  
          end   
          return -1  
        end  
     end      
  
    if exists(select * from Member where MemberInternalNo = @OldMemberInternalNo) begin   
      Delete from Member where MemberInternalNo = @OldMemberInternalNo  
      if @@rowCount <> 1 begin  
        if @@TranCount = 1 begin  
          rollback tran  
        end   
        return -1  
      end  
    end  
  end;  
  Commit Transaction  
end   
  
  
  
  
  
