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