Check for existence and wait…

A friend of mine asked me how to implement this type of logic.  The task was to check for an “existence” of an entry in a table, and if the entry does not exist, – wait for specified time interval, then check again, and only then exit.  Of course the description does not include a positive outcome on either initial check for existence, or the final check.  But the solution must have this logic.  Here’s my 30-minute solution that I worked out for him:

create procedure dbo.sp_CheckForExistence (
   @flag int
  ,@val sysname                           )
as
   if exists (select * from sysusers where name = @val)
      return (0)
   else begin
      set @flag = @flag + 1
      return (@flag)
   end
go
create procedure dbo.sp_Parent (
   @val sysname                )
as
   declare @flag int; set @flag = 0
   declare @threshold int; set @threshold = 2
   while @flag >= 0 begin
      exec @flag = dbo.sp_CheckForExistence @flag, @val
      If @flag = 0 break; --This means that it exists
      If @flag = @threshold break; --We waited and re-tried, but existence is not satisfied, so we quit
      Waitfor delay '00:01:00'
   end
   if @flag = @threshold begin --we know that waiting didn’t help
      /* do something here */
      raiserror ('We waited for %d minutes, but nothing happened.', 0, 1, @threshold) with nowait
   end else if @flag = 0 begin -- no waiting was necessary, the record was there
      /* do something else here */
      raiserror ('We didn''t wait, the record was there.', 0, 1) with nowait
   end else if @flag < @threshold begin
      /* do some other things */
      raiserror ('We waited for %d minute(-s), and it was worth it.', 0, 1, @threshold) with nowait
   end
   return (0)
go
-- Then you execute it as:
exec dbo.sp_Parent N'Joe'