时间戳

首页 > 解决方案 > 带有等待子句的存储过程

sql-server - 带有等待子句的存储过程

问题描述

我有以下 SQL 服务器stored procedure，我的第一个存储过程，需要一些帮助。

是否可以添加某种类型的“WAIT_TRIGGER”？我需要检查一个表，如果表中存在该值，则继续执行 sp。如果不是，则添加 5 分钟等待，然后重试 exec，继续循环此过程，直到值存在。

我不知道我所问的是否需要更多时间来实施，或者这是否可能。

if exists
     (select RUN_DT 
     from PROCESS_TBL
     where 1=1 and RUN_DT = cast(getdate() as date) )
--else   return to top and wait 5 minutes?

所有代码：

USE [MAIN]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[sp_MAIN_insert]
@packagename  as varchar(255)
as
begin
declare @returnhome as varchar(1)
-- set nocount on added to prevent extra result sets from
-- interfering with select statements.
set nocount on;

if @packagename='MAIN_insert'   

begin

if exists
     (select RUN_DT 
     from PROCESS_TBL
     where 1=1 and RUN_DT = cast(getdate() as date) )

begin

declare 
@curr_dt date = cast(getdate() as date),
@prev_dt date = cast(getdate()-1 as date)                
;
insert into CHK_DATA
(run_dt,
db,
[schema],
[table],
comment
)
-------------------
select 
cast(getdate() as date)run_dt,
'MAIN' as 'db',
'dbo' as [schema],
'CHK' as [table],
'test' as comment
from CHK_DATA;
end
end
end
GO

标签： sql-serverstored-procedureswait

解决方案

你可以试试

while 1=1 or (other exit condition)
begin

  if exists(...)
  begin

  end
  else
    waitfor delay '0:05:00'
end

等待 5 分钟，或任何低至 ms 的时间段

推荐阅读