sql job proc

if object_id('everyDay') is not null
drop proc everyDay
go
create proc everyDay
as
declare --声明变量
@id varchar(10),
@createtime varchar(50),
@uid varchar(50),
@times varchar(10),
@month_return varchar(20),
@sumreturn varchar(50),
@sumpay varchar(50),
@balance varchar(50),
@leftM varchar(50),
@rightM varchar(50),
@lastaddtime datetime
begin
	--游标查询需要修改的数据
	DECLARE c CURSOR FOR SELECT * FROM dt_return WHERE balance > 0 and times > 0;
	OPEN c;
        --把值传入变量中
	FETCH next FROM c into @id,@createtime,@uid,@month_return,@sumreturn,@sumpay,@balance,@times,@leftM,@rightM,@lastaddtime;
	--当还有数据时
        while @@FETCH_STATUS = 0 
	begin
		--判断
		IF @lastaddtime is null
		BEGIN
			--id=@id表示当前数据
			update dt_return set lastaddtime = GETDATE() where id = @id;
		END
		--根据lastaddtime判断不是今天，并且离上次时间过了15天
		IF DateDiff(dd,@lastaddtime,getdate())!= 0 and DateDiff(dd,@lastaddtime,getdate()) % 15 = 0
		BEGIN
			update dt_users set frozen_money += @balance * 0.75 where id = @uid
		END
		FETCH next FROM c into @id,@createtime,@uid,@month_return,@sumreturn,@sumpay,@balance,@times,@leftM,@rightM,@lastaddtime;
	end
	close c
	DEALLOCATE c
end


exec everyDay --执行存储过程

上面写好了一个存储过程，放在job里就能定时运行

在SQL中的SQL SERVER代理中新建作业，新建步骤并在命令里输入exec everyDay

新建计划设置运行job时间