Question

SQL Server 里需要定时执行某个存储过程,存储过程怎么写？

SQL Server 里需要定时执行某个存储过程,存储过程怎么写？

数据库：test
表名：biao

id money1 time money2 money3 money4
1 600 2010-1-10 100
2 1200 2010-1-12 200

存储过程是：if DateDiff("d",time,now)<=30 and DateDiff("d",time,now)>0
money3 = money1*0.3
if DateDiff("d",time,now)<=60 and DateDiff("d",time,now)>30
money3 = money1*0.6
if DateDiff("d",time,now)<=365 and DateDiff("d",time,now)>60
money3 = money1*0.9
if DateDiff("d",time,now)>365
money3 = money1

if DateDiff("d",time,now)<=30 and DateDiff("d",time,now)>0
money4 = money1*0.3-money2
if DateDiff("d",time,now)<=60 and DateDiff("d",time,now)>30
money4 = money1*0.6-money2
if DateDiff("d",time,now)<=365 and DateDiff("d",time,now)>60
money4 = money1*0.9-money2
if DateDiff("d",time,now)>365
money4 = money1-money2

时间是每天的01:00执行一次
存储过程的SQL怎么写？？？

Answer 1

sql作业调度，输入信息和SQL句就可以了。
别忘了一点，把SQL Agent 启动一下，不会，不会工作的哦。你可以服务器管理器里让他随os 启动。

Answer 2

create proceduer pro_test
as
if DateDiff("d",time,now)<=30 and DateDiff("d",time,now)>0
money3 = money1*0.3
if DateDiff("d",time,now)<=60 and DateDiff("d",time,now)>30
money3 = money1*0.6
if DateDiff("d",time,now)<=365 and DateDiff("d",time,now)>60
money3 = money1*0.9
if DateDiff("d",time,now)>365
money3 = money1

if DateDiff("d",time,now)<=30 and DateDiff("d",time,now)>0
money4 = money1*0.3-money2
if DateDiff("d",time,now)<=60 and DateDiff("d",time,now)>30
money4 = money1*0.6-money2
if DateDiff("d",time,now)<=365 and DateDiff("d",time,now)>60
money4 = money1*0.9-money2
if DateDiff("d",time,now)>365
money4 = money1-money2
go

在sql管理器中，打开sqlserver代理，选择作业，--》新增作业。
根据步骤，输入exec pro_test，设置执行排程。启用就可以了