AWAIT (Snowflake Scripting) | Snowflake Documentation (original) (raw)

ReferenceScripting referenceAWAIT

Waits for all asynchronous child jobsthat are running to finish or for a specific asynchronous child job that is running for aRESULTSET to finish, then returns when the all jobs have finished or the specific job has finished, respectively.

AWAIT is a blocking call. You can use an AWAIT statement to block other code from running until the AWAIT call completes.

See also:

CANCEL

Syntax¶

AWAIT { ALL | };

Where:

ALL

The stored procedure waits for all asynchronous child jobs that were started before the AWAIT call.

_resultsetname_

The stored procedure waits for the asynchronous child job that is running for the specified RESULTSET to finish.

Usage notes¶

• An asynchronous child job is created when the ASYNC keyword is specified for a query. For more information, see Working with asynchronous child jobs.
• When the ASYNC keyword is specified for a query, the stored procedure can’t access the query results until an AWAIT statement returns the results.
• When you run an asynchronous child job, “fire and forget” isn’t supported. Therefore, if the stored procedure runs a child job that is still running when the stored procedure completes, the child job is canceled automatically.
• Snowflake Scripting supports built-in variables that you can use in the code for stored procedures.
  These variables behave in the following ways for asynchronous child jobs:
  • The SQLID variable is available for the query specified for an asynchronous child job immediately after the asynchronous child job is created.
  • The following built-in variables for exception handlingare available after the AWAIT or AWAIT ALL statement associated with the asynchronous child job that caused the error runs:
    * SQLCODE
    * SQLERRM
    * SQLSTATE
  • The following built-in variables related tothe number of rows affected by DML commandsare available after the AWAIT statement associated with the asynchronous child job for a RESULTSET runs:
    * SQLROWCOUNT
    * SQLFOUND
    * SQLNOTFOUND

  These variables aren’t available when an AWAIT ALL statement runs.

• If an asynchronous child job fails, the AWAIT or AWAIT ALL statement associated with the asynchronous job fails with an error, and execution of the stored procedure stops. For example, the following stored procedure fails and returns an error when execution reaches the AWAIT statement:
  BEGIN
  LET res RESULTSET := ASYNC (SELECT * FROM invalid_table);
  AWAIT res;
  END;
  002003 (42S02): Uncaught exception of type 'STATEMENT_ERROR' on line 2 at position 4 : SQL compilation error:
  Table 'INVALID_TABLE' does not exist or not authorized.

Examples¶

Wait for all asynchronous child jobs to complete:

Wait for an asynchronous child job that is running for a RESULTSET to complete:

For more examples, see Examples of using asynchronous child jobs.