Sql Server Delayed Query Execution: Delay until databases available

In some scenarios, the databases that our query references, may disappear for a period of time and we need to wait until these target databases are available for querying.

Continuously looking at Sql Server Management Studio Object Explorer and wait the database to be online is a quite boring activity.

Common scenario: In Data Warehouse (DWH) environment, the databases we are querying refreshes in regular period from production (live) environment. Generally, this period is daily. During the refresh period, target databases disappear and they are inaccessible until  new version is restored. This process takes several hours for enterprise databases.

This kind of wait operation can be assigned to a T-SQL query, so our query runs immediately when target databases are available (in ONLINE state).

Firstly, here is the stored proc that determines whether databases are in ONLINE state:

CREATE PROC.IS_DB_READY @IS_READY BIT OUTPUT
AS
BEGIN
 SET @IS_READY = 1

 DECLARE @DATABASE_NAME_LIST TABLE (DATABASE_NAME VARCHAR(100))

 -- Insert here the names of the databases
 -- that you're waiting to be online.
 INSERT INTO @DATABASE_NAME_LIST
 SELECT 'myDatabaseName1'

 INSERT INTO @DATABASE_NAME_LIST
 SELECT 'myDatabaseName2'

 ------------------------------------------------------------------------
 IF EXISTS (
   SELECT 1
   FROM @DATABASE_NAME_LIST L
   LEFT JOIN sys.databases D WITH (NOLOCK) ON L.DATABASE_NAME = D.NAME
   WHERE ISNULL(D.state_desc, '') <> 'ONLINE'
   )
 BEGIN
  SET @IS_READY = 0
 END
END

Now we can write the proc that delays the execution until databases are online:

CREATE PROCEDURE DELAY_UNTIL_DB_READY
AS
BEGIN
 DECLARE @IS_DB_READY BIT

 WHILE 1 = 1
 BEGIN
  EXEC IS_DB_READY @IS_DB_READY OUTPUT

  IF @IS_DB_READY = 0
   WAITFOR DELAY '00:00:10'
    -- Wait for 10 seconds.
    -- Equivalent of C# Thread.Sleep(10000);
  ELSE
   BREAK;
 END
END

Now developer only needs to execute the procedure for delaying execution until all databases are in online state.
Sample usage:

EXEC DELAY_UNTIL_DB_READY
-- Your queries to run after databases become online