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.

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