游标嵌套游标实例

CREATE procedure dbo.koo_delete_tbHouseContentHistory_20071031
 
as
 set nocount on

 begin
  --第一次循环
  DECLARE @keyname nvarchar(255)
  DECLARE @stepId int
  DECLARE myCusor1 CURSOR
  for
  select keyname from tbinfokey

  OPEN myCusor1
  FETCH NEXT FROM myCusor1 INTO @keyname

  WHILE @@FETCH_STATUS = 0
  begin
  
   set @stepId=100000
   --第二次循环开始
   DECLARE @id int
   DECLARE @title nvarchar(255)
   DECLARE @htmlpath nvarchar(255)
   DECLARE myCusor2 CURSOR
   for

   select top 1000 id,title,htmlpath from dbo.tbHouseContentHistory
   where id>@stepId and id in
   (select id from dbo.tbHouseContentHistory where title like '%'+@keyname+'%' or content like '%'+@keyname+'%') 
   order by id desc

   OPEN myCusor2
   FETCH NEXT FROM myCusor2 INTO @id,@title,@htmlpath

   WHILE @@FETCH_STATUS = 0
   begin
    insert into tbinfodelnew(title,contentID,htmlpath,kind) values (@title,@id,@htmlpath,0)
                        delete from tbhousecontenthistory where id=@id

    FETCH NEXT FROM myCusor2 INTO @id,@title,@htmlpath
   end
   CLOSE myCusor2
       DEALLOCATE myCusor2
   ----第二次循环结束

   FETCH NEXT FROM myCusor1 INTO @keyname
  end
  CLOSE myCusor1
      DEALLOCATE myCusor1

 

  set @stepId=@stepId+10000

 end
GO