Sql server中cursor的使用



ALTER PROCEDURE [dbo].[usp_cm_mapping_reference_id_commonplace]
AS
SET NOCOUNT ON;
BEGIN
 declare @master_location_id int;
 declare @commonplace_name varchar(300);

 declare my_cursor cursor for
 select master_location_id,common_place_name
 from master_location_common_place group by master_location_id,common_place_name
 having count(*)>1

 open my_cursor
 fetch next from my_cursor into @master_location_id,@commonplace_name
 while @@FETCH_STATUS= 0
 Begin
  --logic
  delete from master_location_common_place where master_location_id=@master_location_id and common_place_name=@commonplace_name
  and master_location_common_place_id not in
   (select min(master_location_common_place_id) from master_location_common_place
   where master_location_id=@master_location_id and common_place_name=@commonplace_name)

  --Get id of insert
  set @new_master_location_id=SCOPE_IDENTITY();
  
  --reference_id
  select @reference=reference_id from master_location where master_location_id=@master_location_id;
  if(isnull(@reference,'')='')
  Begin 
   update master_location set reference_id=@reference_id where master_location_id=@master_location_id;
  End
  else
  Begin
   if(isnull(@reference_id,'')!='')
   Begin
    if(charIndex(cast(@reference_id as varchar),@reference)=0)
    Begin
     set @reference=@reference+','+cast(@reference_id as varchar);
     update master_location set reference_id=@reference where master_location_id=@master_location_id;
    End
   End
  End

  fetch next from my_cursor into @master_location_id,@commonplace_name
 End
 close my_cursor
 deallocate my_cursor
END