預存程序,使用遊標比較資料

ALTER PROCEDURE [dbo].[P_DeleteRepeatRecord]
 @siteName varchar(50),
 @goodsId varchar(20)
AS
BEGIN

declare @SelectSql varchar(MAX),@tableName varchar(50)
set @tableName='goods_price_history_'+@siteName
set @SelectSql='
declare my_cursor  cursor scroll
 for select id,goods_price,createDate from '+@tableName+'
  where goods_id in('+@goodsId+') order by createDate asc

--聲明變數
 declare @recoderID int
 ,@recoderPrice decimal(18,2)
 ,@tempRecordPrice decimal(18,2)
 ,@tempRecordID bigint
 ,@recordTime datetime
 ,@tempRecordTime datetime
 

 open my_cursor
 
 Fetch next from my_cursor into @recoderID ,@recoderPrice,@tempRecordTime
 while(@@FETCH_STATUS =0)

  begin
     if @tempRecordPrice=@recoderPrice
       begin
      -- print delete
         --刪除
        delete '+@tableName+' where id =@recoderID
        --更新時間
       update  '+@tableName+'
        set lastdate=@tempRecordTime
        where id =@tempRecordID
          end
      else
      begin
      set @tempRecordPrice=@recoderPrice--賦值
      set @tempRecordID =@recoderID
     -- print no
     
      end
 
 
 fetch next from my_cursor into @recoderID ,@recoderPrice,@tempRecordTime

 end
 --關閉遊標
close my_cursor
deallocate my_cursor
'
exec(@SelectSql)
END