[MSSQL]sql 儲存過程定時執行方法

-- 建立儲存過程，每隔 30 秒鐘執行一次。
create procedure usp_daemon
as
set nocount on
while 1=1
begin
update [user] set type=0 where data<getdate();
delete from [tab] where userid in (select userid from [user] where type=0);
waitfor delay '00:00:30'
end
go

-- 將 sql server 例項設定為“在例項啟動時掃描可自動執行的儲存過程”
sp_configure 'show advanced options',1
reconfigure
sp_configure 'scan for startup procs',1
reconfigure
sp_configure 'show advanced options',0
reconfigure

-- 將儲存過程設定為“例項啟動時自動執行”
sp_procoption 'usp_daemon','startup','true'