Run SQL command in loop until reach time condition-CodePudding

I need to solve following problem (in TSQL):

DECLARE 
    ,@CurrentTime AS DATETIME = GETDATE()
    ,@KillTime AS DATETIME
    ,@ProcessToKill AS VARCHAR(100)
    ,@KillProcessCommand AS VARCHAR(100)
SET @KillTime = comming from concrete select
SET @ProcessToKill = process ID comming from concrete select
SET @KillProcessCommand = kill command

e.g.: CurrentTime = 2022-06-01 16:00:00.830 ; KillTime = 2022-06-01 15:55:00.000

My loop should go in circle, until CurrentTime >= KillTime - if fulfilled, then kill the process, otherwise repeat each 5 minutes.

Could someone help? Thanks

CodePudding user response：

You don't need to run a loop to wait for a time, you can just use the WAITFOR command, e.g.

PRINT 'Time start: '   CONVERT(VARCHAR(8), GETDATE(), 8);
DECLARE @Time VARCHAR(8) = CONVERT(VARCHAR(8), DATEADD(SECOND, 5, GETDATE()), 8);

WAITFOR TIME @Time;

PRINT 'Time End: '   CONVERT(VARCHAR(8), GETDATE(), 8);

Example on db<>fiddle