CODEWITHSUNDEEP
sql-serverstored-procedureswait
Toby
Toby

Reputation: 145

Store Procedure with Wait Clause

I have the below SQL server stored procedure, my first stored procedure and need some help.

Is it possible to add some type of "WAIT_TRIGGER"? I need to check a table and if the value exist in the table then proceed to exec the sp. If not, add a 5 minute wait and then retry exec, continue to loop this process until value exist.

I'm unware if what I'm asking involves a lot more time to implement or if this is even possible.

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?

All code:

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

Upvotes: 0

Views: 2053

Answers (1)

Stu
Stu

Reputation: 32619

You can try

while 1=1 or (other exit condition)
begin

  if exists(...)
  begin

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

to wait for 5 minutes, or any period of time down to ms

Upvotes: 1

Related Questions