WHILE EXISTS Error Handling/ Raise Error

Question

I am adding a step in my SQL job where it does a WHILE EXISTS check for me before executing the last step. What it does is it will wait for 15minutes to see if the table I am checking has been populated with datetime data, in order to have the correct datetime data it should be greater than midnight. If it is greater than midnight I will go ahead and move to the last step. But I was stuck to add an error handling mechanism to close out the step with error after trying for a couple of time for 15minutes... Here is the query below:

WHILE EXISTS ( SELECT * FROM ETLTimeCheck WHERE EXEC_END_TIME is NULL OR EXEC_END_TIME > DATEADD(D,0,DATEDIFF(D,0,GETDATE())))
    BEGIN
            WAITFOR DELAY '00:15:00'
    END

So I started with this but got stuck figuring out the error handling/ how to raise an error,

WHILE EXISTS ( SELECT * FROM ETLTimeCheck WHERE EXEC_END_TIME is NULL OR EXEC_END_TIME > DATEADD(D,0,DATEDIFF(D,0,GETDATE())))
    BEGIN
        BEGIN TRY
            WAITFOR DELAY '00:15:00'
        END TRY
    END

BEGIN CATCH 
-- The Error Message
END CATCH

Answer 1

You will only get into the "Catch" if the "waitFor Delay" fails (which is unlikely to happen).

If you want to raise an alert/error everytime it waits 15 min, then the "raiserror" should be place right before/after your WAITFOR.

You can check this page out to get info about the "Raiserror" command : https://learn.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-ver16

Be carefull with the severity as some error will end the script execution.

So if you want you code to stop and send an error after 3h, it would look like this:

DECLARE
    @current_time_now time = 
        SYSDATETIME(),
    @current_time_plus_three_hours time = 
        DATEADD(HOUR, 3, SYSDATETIME());
declare @flag int =0;

WHILE
    @current_time_now < @current_time_plus_three_hours
    

BEGIN
    set @flag = @flag + 1
    WAITFOR DELAY '00:15:00.000';
    if (@flag > 12)
        raiserror('my error',20,1) with log
END 

Answer 2

different loops

It would make more sense to me to use a WHILE loop to check for when the current time is less than three hours in the future, and raise an error when that condition becomes true.

DECLARE
    @current_time_now time = 
        SYSDATETIME(),
    @current_time_plus_three_hours time = 
        DATEADD(HOUR, 3, SYSDATETIME());

WHILE
    @current_time_now < @current_time_plus_three_hours

BEGIN
BEGIN TRY
    WAITFOR DELAY '00:00:15.000';
END TRY;
END;
BEGIN CATCH
    RAISERROR('Time''s up, bucko.', 0, 1) WITH NOWAIT;
END CATCH;