Running a script in SSMS with a specific offset using WAITFOR

Hey All,

I have a stored procedure scheduled to run a job every ten minutes that I was doing some testing on.

It usually takes about a minute to run, so when I'm testing it out, I like to run it on the 5th minute of every ten minute period to be sure that it doesn't interfere with the jobs run.

To make sure this happens every time, I created a quick snippet to find the current offset needed in a WAITFOR statement so it'll run the next time the time ends in a 5.
This doesn't account for seconds intentionally because I want there to be no delay if the GETDATE() statement returns a value in the 5th minute.

DECLARE @wait    AS INT, 
        @current AS INT, 
        @waitfor AS NVARCHAR(10) 

SELECT @current = Cast(RIGHT(Cast(Datepart(mi, Getdate()) AS VARCHAR(2)), 1) AS 
                       INT) 

SELECT @wait = CASE 
                 WHEN @current = 5 THEN 0 
                 WHEN @current < 5 THEN 5 - @current 
                 ELSE 15 - @current 
               END 

SELECT @waitfor = '00:' 
                  + RIGHT('00' + Cast(@wait AS VARCHAR(2)), 2) 
                  + ':00' 

SELECT @waitfor 

WAITFOR delay @waitfor 
-- This could be flattened to  look like this 
/* 
SET @waitfor = '00:' + right('00' + cast(( 
        CASE  
          WHEN (cast(right(cast(datepart(mi, getdate()) AS VARCHAR(2)), 1) AS INT)) = 5 
            THEN 0 
          WHEN (cast(right(cast(datepart(mi, getdate()) AS VARCHAR(2)), 1) AS INT)) < 5 
            THEN 5 - (cast(right(cast(datepart(mi, getdate()) AS VARCHAR(2)), 1) AS INT)) 
          ELSE 15 - (cast(right(cast(datepart(mi, getdate()) AS VARCHAR(2)), 1) AS INT)) 
          END 
        ) AS VARCHAR(2)), 2) + ':00' 
WAITFOR DELAY @waitfor 
*/

Facebook Social Comments

Home

Running a script in SSMS with a specific offset using WAITFOR