Store Procedure with Wait Clause

Question

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

Have you heard of the SQL Server Agent? https://dotnettutorials.net/lesson/how-to-schedule-job-in-sql-server/ https://www.sqlservercentral.com/articles/sql-server-job-scheduling etc — Caius Jard, Mar 12 '21 at 17:43
https://stackoverflow.com/questions/7676164/how-to-wait-for-2-seconds — Raseena Abdul, Mar 12 '21 at 17:45
Sounds like you are trying to [use table as a queue](https://rusanu.com/2010/03/26/using-tables-as-queues/). Waiting in a stored procedure that will run **forever** is a bad choice for many reasons. Try searching on "sql server use table as queue". — SMor, Mar 12 '21 at 19:53
@SMor I also thought about using a .bat file, might be eaiser? — Toby, Mar 12 '21 at 20:52

Stu · Answer 1 · 2021-03-12T18:09:38.203

1

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

edited Mar 12 '21 at 18:09

answered Mar 12 '21 at 17:48

Stu

30,392
6
14
33

But will that waitfor delay return to top and check if exist statement again? – Toby Mar 12 '21 at 18:04
1

I have edited above to show example of how you might implement. It's one way to do it - it might be more appropriate just to run as a sql agent job on a schedule, it depends on your use-case. – Stu Mar 12 '21 at 18:10

Store Procedure with Wait Clause

1 Answers1