記錄一下自己的游標小腳本

---游標迴圈遍歷--
begin
  declare @id int,@temp int,@error int
  set @error=0
  begin tran --申明事務
  --業務--
  update SmartPromoter set CustomerID=a.ID from SmartCustomer a,SmartPromoter b 
     where (a.Mobile=b.Mobile or a.MobileBackup=b.Mobile) and b.CustomerID is null and len(b.Mobile)=11

  declare promoter_cursor cursor for(select ID from SmartPromoter where CustomerID is null and len(Mobile)=11) --申明游標
   --打開游標--
  open promoter_cursor 
  --開始迴圈游標變數--
  fetch next from promoter_cursor into @temp
  while @@FETCH_STATUS=0 --返回被 FETCH語句執行的最後游標的狀態--
    begin
      insert into SmartCustomer([Name],[Gender],[Mobile],[WeChat],[CreateTime],[ChannelID],[Remark],[CreateUserID],[Deposit],[Coupon],[Point],[CurrentExploitUserID],[CurrentManagerUserID],
      [VisitTimes],[ConsultTimes],[IsBlacklist],[IsGreylist],[HasComplain],[HasAppointment],[WechatBindTime],[MemberCategoryID],[CashCardTotalAmount]) 
      select Name,2,Mobile,Wechat,CreateTime,126,Remark,1,0,0,0,2,2,0,0,0,0,0,0,CreateTime,1,0 from SmartPromoter a where ID=@temp select @id = @@IDENTITY
      set @error= @error + @@ERROR --記錄每次運行sql後是否正確，0正確
      update SmartPromoter set CustomerID=@id where ID=@temp
      set @error= @error + @@ERROR --記錄每次運行sql後是否正確，0正確
        fetch next from promoter_cursor into @temp --轉到下一個游標
    end
  --業務--
  insert into SmartPromoter([Name],[Mobile],[Address],[Status],[Account],[Password],[CreateTime],[Wechat],[CustomerID],[Balance])
    select a.Name,a.Mobile,a.Address,1,a.Mobile,'123456',a.CreateTime,a.WeChat,a.ID,0 from SmartCustomer a where  not exists (select ID from SmartPromoter b where a.ID=b.CustomerID) and LEN(Mobile)=11
   if @error=0
      begin
        commit tran   --提交事務
      end
      else
      begin
        rollback tran --回滾事務
      end
  close promoter_cursor  --關閉游標
  deallocate promoter_cursor   --釋放游標
end