CREATE PROCEDURE [dbo].[sp_rrl_goods_auto_online_or_offline]
AS
BEGIN
declare @goods_id INT;
declare @deletemark DATE;
declare @online_time DATE;
declare @downline_time DATE;
declare @now_time DATE;
declare @temp_goods table (goods_id int primary key, deletemark DATE, online_time DATE, downline_time DATE);
insert into @temp_goods (goods_id, deletemark, online_time, downline_time)
select id,deletemark,online_time,downline_time from rrl_goods WHERE goods_type = 881AND is_delete = 0;
SET @now_time = GETDATE();
while (select count(*) from @temp_goods) > 0
begin
select top 1
@goods_id = goods_id,
@deletemark = deletemark,
@online_time = online_time,
@downline_time = downline_time from @temp_goods;
--上架
if @online_time <= @now_time AND @deletemark isnot null
begin
UPDATE rrl_goods SET deletemark = null WHERE id = @goods_id;
insert into wx_temp VALUES (@now_time, 'online', @goods_id);end
--下架
if @downline_time <= @now_time AND @deletemark is null
begin
UPDATE rrl_goods SET deletemark = @now_time WHERE id = @goods_id;
insert into wx_temp VALUES (@now_time, 'offline', @goods_id);end
-- 从临时表中删除数据
delete from @temp_goods where goods_id = @goods_id;
endEND